Return a LINQ Query with Transformed Data

Given the following method:

        public  IQueryable<StageDriverPayroll> GetNewStageRecords(StageType typeOfStage)
        {
                var stageQuery = 
                    from st 
                    in _db.StageDriverPayrolls.Where(t => !t.Processed && t.Inserted < DateTime.Today.AddDays(-1))
                    let Trimmed_Trip_Id = st.Trip_Id == null ? null : st.Trip_Id.TrimStart("0".ToCharArray())
                    [etc...]
                    select new StageDriverPayroll { 
                           StageRecordId = st.StageRecordId
	                      ,Trip_Id = Trimmed_Trip_Id
	                      ,TpRef_Doc_Type = st.TpRef_Doc_Type
	                      ,[etc...] };

                return stageQuery.AsQueryable<StageDriverPayroll>();
        } 

Open in new window


I am trying to strip out the leading zeroes in input data and return that data, using the same names and structure that a simpler query would produce (i.e. with a simple 'select st').

The return collection is empty and contains this error:

Explicit construction of entity type 'StageDriverPayroll' in query is not allowed.

Removing the type from the select portion of the statement, making it anonymous, results in a compiler error:

Instance argument: cannot convert from 'System.Linq.IQueryable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<SPI.UIM.DriverPayroll.StageDriverPayroll>'      C:\Source\Simons\Payroll\Stage2PayrollLib\Model\StageRepository.cs

How can I return the result of the query with the string transformations I need?
EdwardAFAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi EdwardAF;

You can not create an instance of StageDriverPayroll in your select statement because it is of the same type as the input object. What you will need to do is create a class with fields that will match those that you wish to return and place that in the select clause to replace StageDriverPayroll. If that is not what you want to do then you will need to make the query without the modification of st.Trip_Id and then loop through the results and make the modifications then.

// You can make these changes to make it work.
public  IQueryable<MyStageDriverPayroll> GetNewStageRecords(StageType typeOfStage)
{
    var stageQuery = 
        from st 
        in _db.StageDriverPayrolls.Where(t => !t.Processed && t.Inserted < DateTime.Today.AddDays(-1))
        let Trimmed_Trip_Id = st.Trip_Id == null ? null : st.Trip_Id.TrimStart("0".ToCharArray())
        [etc...]
        select new MyStageDriverPayroll { 
               StageRecordId = st.StageRecordId
              ,Trip_Id = Trimmed_Trip_Id
              ,TpRef_Doc_Type = st.TpRef_Doc_Type
              ,[etc...] };

    return stageQuery.AsQueryable<MyStageDriverPayroll>();
}

...

public class MyStageDriverPayroll
{
	public int StageRecordId { get; set; }
	public string Trip_Id { get; set; }
	[etc...]
}     

Open in new window


Fernando
0
 
käµfm³d 👽Commented:
If I'm not mistaken, this error is only related to EF. You could do what you are attempting to do in Linq to Objects.

For example, the following is perfectly acceptable:
List<MyClass> list = new List<MyClass>()
{
    new MyClass()  { Prop1 = "00Hello", Prop2 = "00World!" },
    new MyClass()  { Prop1 = "00Hello1", Prop2 = "00World!1" },
    new MyClass()  { Prop1 = "00Hello2", Prop2 = "00World!2" },
    new MyClass()  { Prop1 = "00Hello3", Prop2 = "00World!3" },
};

var stageQuer = from item in list
                let Trimmed_Trip_Id = item.Prop1 == null ? null : item.Prop1.TrimStart("0".ToCharArray())
                select new MyClass() { Prop1 = Trimmed_Trip_Id, Prop2 = item.Prop2 };

foreach (var item in stageQuer)
{
    Console.WriteLine(item.Prop1);
}

Open in new window

0
 
Fernando SotoRetiredCommented:
@ kaufmed;

By the looks of the error that EdwardAFis getting back and this in the query, db.StageDriverPayrolls, it looks like Linq to SQL.

Fernando
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
käµfm³d 👽Commented:
it looks like Linq to SQL.
Could be. The post I found associated with this error mentioned EF, but now that I think about it, L2S sounds plausible too  :  )

The reason I saw mentioned for this logic not being allowed was that it would pollute the cache with dirty "entities", so it was apparently a move by MS to prevent confusion on a number of levels.
0
 
Fernando SotoRetiredCommented:
@ kaufmed;

I could not find any references in my travails on the reason for this but the only thing I can think of is that on querying a datatable and changing one of its values and using a entity class to save the changes of which Linq to SQL will track for modifications will leave that record out of sync event before returning the record to the DataContext.

Fernando
0
 
EdwardAFAuthor Commented:
It is L2S.  What you say makes sense, and with a little more research, I can confirm the reason for this behavior.  It leaves the set in an inconsistent state.  Instead of the extra class, which does work btw, I performed a simple select of all elements, then iterated the resulting list to transform the data.  It saved it back to the source table, but in my case, that doesn't matter, so I went with it.

Thank you for the fast and accurate responses!
0
 
Fernando SotoRetiredCommented:
Not a problem, always glad to help.

Have a great day
Fernando
0
 
käµfm³d 👽Commented:
Hooray! I helped  = )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.