?
Solved

Return a LINQ Query with Transformed Data

Posted on 2011-04-20
8
Medium Priority
?
664 Views
Last Modified: 2013-12-16
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?
0
Comment
Question by:EdwardAF
  • 4
  • 3
8 Comments
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 1800 total points
ID: 35437986
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35438008
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 35438024
@ 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 200 total points
ID: 35438036
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
 
LVL 64

Assisted Solution

by:Fernando Soto
Fernando Soto earned 1800 total points
ID: 35438093
@ 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
 

Author Closing Comment

by:EdwardAF
ID: 35441701
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
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 35441768
Not a problem, always glad to help.

Have a great day
Fernando
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35444929
Hooray! I helped  = )
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question