Select TOP 1 for each FK in list using Entity Framework

entity framework select top 1 order by desc
linq select top 1 order by desc c#
entity framework save object with foreign key
get list from entity framework in c
entity framework select all to list
entity framework where tolist
ef core update related entities
entity framework navigation properties

I have a large table where I'm trying to select the top 1 row for each FK in a list.

My table is laid out as:

ChangeId | AssetId | Timestamp
1          1         123
2          2         999
3          1         3478
4          3         344
5          2         1092

Where ChangeId is my PK, AssetId is my FK and Timestamp is the value I'm trying to select.

If I try the following:

var results =

from Asset in _context.Asset
join change in _context.Change on Asset.AssetId equals change.AssetId into potentialChange
from actualChange in potentialChange.OrderByDescending(y => y.ChangeId).Take(1)
select 
{
   AssetId,
   Timestamp
}

Where my expected result would be:

[
  {
    AssetId: 1,
    Timestamp: 3478
  },
  {
    AssetId: 2,
    Timestamp: 1092
  },
  {
    AssetId: 3,
    Timestamp: 344
  }
]

This query flags up the The LINQ expression could not be translated and will be evaluated locally. which is not suitable for a production rollout.

Running a foreach loop and selecting each item out 1 by 1 works, not it's not a performant solution.

Is there a suitable way to achieve the above?


Try to group it by AssetId and take max from each group

var results =

from Asset in _context.Asset
join change in _context.Change on Asset.AssetId equals change.AssetId into potentialChange
group potentialChange by potentialCharge.AssetId into g
select 
{
    g.Key,
    g.Max().Timestamp
}

Linq to SQL, Hi! How do i write a SQL query like this in Linq? SELECT TOP 1 * FROM table ORDER BY col DESC. In this solution woulden't you get a large result set back from the DB with all the rows and then perform a .First() on that? I have recently inherited a project that makes use of WCF Data Services with the Entity Framework being used as the ORM to facilitate this with Microsoft SQL Server 2008 R2. I had a requirement to only ever retrieve a maximum of 100 items no matter the query. In SQL this is easy and can be achieved using the SELECT TOP (N).. syntax.


Use Group By as follows:

List<MyTable> data = new List<MyTable>()
{
     new MyTable(){ChangeId = 1, AssetId = 1, Timestamp = 123},
     new MyTable(){ChangeId = 2, AssetId = 2, Timestamp = 999},
     new MyTable(){ChangeId = 3, AssetId = 1, Timestamp = 123},
     new MyTable(){ChangeId = 5, AssetId = 3, Timestamp = 123},
     new MyTable(){ChangeId = 5, AssetId = 2, Timestamp = 123},
};

var expectedData = data.OrderByDescending(d => d.Timestamp).GroupBy(d => d.AssetId).Select(g => new
{
     AssetId = g.Key,
     TimeStamp = g.First().Timestamp

}).ToList();

This will give your expected result.

[SOLVED], If you wanted to find the top 10 Videos with the most number of Tags, you would probably find it easier, but in fact what you want to do now is  I want a list of the most frequent category. My objects are: Category: catId, catName Movement: Movid, movDate, movMount, catId. I think it would have to raise it with a "Group By" query (grouping by catId and getting those more) (Im using Entity Framework 6 in c#) From already thank you very much!


Try using .First() instead of .Take(1)

LINQ How to take one record and skip rest c#

Managing One To Many Relationships With Entity Framework Core , Entity Framework Core offers a number of approaches to the creation and If the author is not currently being tracked, a command is issued to select it from the database. Then a exec sp_executesql N'SELECT TOP(1) [e]. The book is created and has a valid value applied to its foreign key property. All rights reserved. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more Entity Framework select from a list of ids obtained from another E.F select query


Tutorial: Read related data, You specify eager loading in Entity Framework Core by using the Include and When the entity is first read, related data isn't retrieved. A query is sent to the database each time you try to get data from a navigation property Run the app and select the Courses tab to see the list with department names. Using lambda expression unable to do join, group by and where condition at a time in entity framework core Get rows on a condition from dbcontext entity into datatable using LINQ Advertise


Tutorial: Read related data with EF in an ASP.NET MVC app , NET MVC 5 applications using the Entity Framework 6 Code First and Visual Studio. For information about the tutorial series, see the first  If your code is not using the default code first conventions, then you can use ForeignKey attribute, so that code first can identify that it contains the foreign key. In the following example, the AuthorBiographyId property in the AuthorBiography entity does not follow the Entity Framework convention for foreign key names, and it will create an


Eager Loading in Entity Framework, Learn about eager loading queries in entity framework 6.x. In the following example, it gets all the students from the database along with its standards using the CAST(NULL AS int) ELSE 1 END AS [C1] FROM (SELECT TOP (1) [Extent1​]. Subscribe to EntityFrameworkTutorial email list and get EF 6 and EF Core  Configure One-to-Zero-or-One Relationship in Entity Framework 6. Here, you will learn to configure One-to-Zero-or-One relationships between two entities. We will implement a one-to-Zero-or-One relationship between the following Student and StudentAddress entities.