CODEWITHSUNDEEP
c#sqlentity-frameworklinqone-to-many
James
James

Reputation: 23

How to get the whole record from a collection which has latest dateTime using linq?

I have a entity that has many columns and has navigation property to another entity in a one to many relationship, now in my navigation property I have a col dateTimeStamp, Now I want to get records including my navi property records but only that which has the latest dateTimeStamp. How can we do it using Lambda expression linq query ?

Entities looks like

public class EntityA {
   int entityAId {get;set;}
   string desc {get;set;}
   ...

   <ICollection<EntityB>> EntitiesB {get;set;}
}

public class EntityB {
   int entityAId {get;set;}
   int entityBId {get;set;}
   dateTime dateTimeStamp {get;set;}
   ...

   EntityA EntityANavi {get;set;}
}

}

So each EntityA has many EntityB but I want only that EntityB record that has latest dateTime, how can I do that using lambda expression and linq operators?

Data looks like :-

EntityA 
EntityAId  Desc     ...
 12         sample1
 22         sample2

EntityB
EntityBId  EntityAId  DateTimeStamp         ....
 1           12         2020-04-01-10:10:01
 1           12         2020-04-01-10:10:21
 1           12         2020-04-01-10:11:01
 2           22         2020-04-01-11:10:51
 2           22         2020-04-01-11:11:01

Result: an Object of

EntityA :{
   {entityAId : 12,
    desc: sample1
    ...
    EntitiesB :{
     EntityB:{ EntityAId:12, EntityBId :1, DateTimeStamp :2020-04-01-10:11:01  ....  } 
   },
   {entityAId : 22,
    desc: sample2
    ...
    EntitiesB :{
     EntityB:{ EntityAId:22, EntityBId :2, DateTimeStamp :2020-04-01-11:11:01  ....  } 
   }
 }

}

I tried using EntityA.Include( a => a.EntitiesB.orderByDescending(b => b.DateTimeStamp).First() ) but I keep on getting error

Upvotes: 0

Views: 529

Answers (3)

Harald Coppoolse
Harald Coppoolse

Reputation: 30454

I want to get records including my navi property records but only that which has the latest dateTimeStamp.

var entityAWithItsNewestEntityB = dbContext.EntitiesA.Select(entityA => new
{
    // Select the EntityA properties that you plan to use:
    Id = entityA.Id,
    Name = entityA.Name,
    ...

    NewestEntityB = entityA.EntitiesB
        .OrderByDescending(entityB => entityB.DateTimestamp)
        .Select(entityB => new
        {
             // Select only the EntityB properties that you plan to use
             Id = entityB.Id,
             Name = entityB.Name,
             ...

             // Foreign key not needed, you already know the value:
             // EntityeAId = entityB.EntityAId,
        })
        .FirstOrDefault(),
    });

In words: from every EntityA object in the collection of EntityA objects, select one or more properties. Also order all EntityB objects of this EntityA by descending DateTimeStamp, such that the newest EntityB will be first. From this ordered sequence of EntityB objects, select several properties. From the result take only the first one, or null if EntityA has no EntityB objects at all.

Simple comme bonjour!

Upvotes: 1

Jodn
Jodn

Reputation: 324

I assume you are using Entity Framework. For EFcore the good news is, it will come in the future and is available in preview 5.0 for efcore: https://github.com/dotnet/efcore/commit/21b9a35db594f7a383e855f922babbe54b3d38c5

As for now in an eager loading scenario, as soon as you .Include you're loading any object you are referring to. You can load the required Entities for EntityB and include EntityA to them. A working solution would be:

List<EntityA> listA = ctx.EntityA.Include(k=>k.EntitiesB).ThenInclude(k=>k.EntityANavi)
                     .select(k=>k.EntitiesB.OrderByDescending(l=>l.DateTimeStamp).FirstOrDefualt())
                     .ToList().Where(EntityANavi != null).Select(k=>k.EntityANavi);

Essentially can loading everything and select those objects which are need. After Including EntitiesB, i included EvntityANavi again to make sure the relation is retained. After that just select, "filter" and forget. (.ToList() is to actually load the data and make sure you only have one query running for the datareader, since we keep going on on the query)

Upvotes: 1

Badran Yahyaoui
Badran Yahyaoui

Reputation: 81

try :

var query = EntityA.OrderBy(a => a.EntitiesB.Min(b => b.dateTimeStamp.Date))
                .ToList()

Upvotes: 0

Related Questions