Entity Framework LINQ for finding sub items from LastOrDefault parent

Question

I have few related objects and relation is like

    public class Project
{
    public List<ProjectEdition> editions;
}

public class ProjectEdition
{
    public List<EditionItem> items;
}

public class EditionItem
{

}

I wanted to fetch the EditionItems from Last entries of ProjectEditions only for each Project

Example

Project#1 -> Edition#1 [contains few edition items ] , Edition#2 [contains few edition items]

Project#2 -> Edition#1 ,Edition#2 and Edition#3

My required output contains EditionItems from Edition#2 of Project#1 and Edition#3 of Project#2 only . I mean EditionItems from latest edition of a Project or last edition of a Project only

To get this i tried this query

 List<EditionItem> master_list = context.Projects.Select(x => x.ProjectEditions.LastOrDefault())
                                      .SelectMany(x => x.EditionItems).ToList();

But its returns error at LatsOrDefault() section

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method '---------.Models.ProjectEdition LastOrDefault[ProjectEdition](System.Collections.Generic.IEnumerable`1

so how can i filter for last edition of a project and then get the list of EditionItems from it in a single LINQ call

As your exception says, `LastOrDefault()` is not supported by your provider. And if you think about it, when you work with SQL term of "last" does not make sense if no sort order is applied, as records can come from the server without particular order. — trailmax, Apr 13 '17 at 09:23
Then how can i achieve the same ? I am using SQL Server entity framework model — Sebastian, Apr 13 '17 at 09:32
You must choose by which property in `ProjectEdition` you want to order the entities. Let's say there is a property `Id` in there, then you can use `List master_list = context.Projects.Select(x => x.ProjectEditions.OrderByDescending(pe => pe.Id).FirstOrDefault()).SelectMany(x => x.EditionItems).ToList();` — granit, Apr 13 '17 at 09:37
@granit indeed, exactly that. You should make it as an answer. — trailmax, Apr 13 '17 at 09:51

trailmax · Answer 1 · 2017-04-13T10:29:41.207

Granit got the answer right, so I won't repeat his code. I would like to add the reasons for this behaviour.

Entity Framework is magic (sometimes too much magic) but it yet translates your LINQ queries into SQL and there are limitations to that of what your underlying database can do (SQL Server in this case).

When you call context.Projects.FirstOrDefault() it is translated into something like Select TOP 1 * from Projects. Note the TOP 1 part - this is SQL Server operator that limits number of rows returned. This is part of query optimisation in SQL Server. SQL Server does not have any operators that will give you LAST 1 - because it needs to run the query, return all the results, take the last one and dump the rest - this is not very efficient, think of a table with a couple (bi)million records.

So you need to apply whatever required sort order to your query and limit number of rows you return. If you need last record from the query - apply reverse sort order. You do need to sort because SQL Server does not guarantee order of records returned if no Order By is applied to the query - this is due to the way the data is stored internally.

When you write LINQ queries with EF I do recommend keep an eye on what SQL is generated by your queries - sometimes you'll see how complex they come out and you can easily simplify the query. And sometimes with lazy-loading enabled you introduce N+1 problem with a stroke of a key (literally). I use ExpressProfiler to watch generated SQL, LinqPad can also show you the SQL queries and there are other tools.

Useful explaination! – Mukesh Modhvadiya Apr 13 '17 at 10:32 — Mukesh Modhvadiya, Apr 13 '17 at 10:32

Mukesh Modhvadiya · Answer 2 · 2017-04-13T12:10:52.280

2

List<EditionItem> master_list = context.Projects
                                .Select(p => p.editions.LastOrDefault())
                                .SelectMany(pe => pe.items).ToList();

IF LastOrDefault not supported you can try using OrderByDescending

List<EditionItem> master_list = context.Projects
              .Select(p => p.editions.OrderByDescending(e => e.somefield).FirstOrDefault())
              .SelectMany(pe => pe.items).ToList();

edited Apr 13 '17 at 12:10

answered Apr 13 '17 at 09:10

Mukesh Modhvadiya

2,178
2
27
32

can you try using OrderByDescending, p.editions.OrderByDescending().FirstOrDefault() ? – Mukesh Modhvadiya Apr 13 '17 at 09:34
@JibinMathew, also check make sure your context.Projects return IEnumerable, so try using context.Projects.AsEnumerable().Select(). – Mukesh Modhvadiya Apr 13 '17 at 09:45
down voting with useful comments helps! – Mukesh Modhvadiya Apr 13 '17 at 10:00
@MukeshModhvadiya it wasn't useful before you added the `order by` into the question - it was just the same as the OP had in the question. With the edit it is helpful indeed, down-vote removed. – trailmax Apr 13 '17 at 10:12
1

@trailmax, thank you very much for explaination, I have executed it and posted working query! As soon as I noticed LastOrDefault not supported I added comment for Orderby, and updated answer later! thnx :) – Mukesh Modhvadiya Apr 13 '17 at 10:16
1

@MukeshModhvadiya You might consider removing the `AsEnumerable` suggestions because it's a terrible idea to load the whole table in memory and then execute LINQ to Objects queries where things like lazy/eager loading has to be taken into account. – Ivan Stoev Apr 13 '17 at 10:39

score 2 · Answer 3 · edited May 23 '17 at 12:25

You cannot use method LastOrDefault() or Last() as discussed here.

Insetad, you can use OrderByDescending() in conjunction with FirstOrDefault() but first you need to have a property in you ProjectEdition with which you want to order the entities. E.g. if ProjectEdition has a property Id (which there is a good chance it does), you can use the following LINQ query:

List<EditionItem> master_list = context.Projects.Select(
                  x => x.ProjectEditions
                      .OrderByDescending(pe => pe.Id)
                      .FirstOrDefault())
                      .SelectMany(x => x.EditionItems).ToList();

score -1 · Answer 4 · edited Apr 13 '17 at 09:47

-1

from p in context.project
from e in p.projectEdition.LastOrDefault()
select new EditionItem
{
    item1 = e.item1
}

Please try this

edited Apr 13 '17 at 09:47

Komal12

3,340
4
16
25

answered Apr 13 '17 at 09:08

Kinnal

21
5

Entity Framework LINQ for finding sub items from LastOrDefault parent

4 Answers4