Getting a single entity property from collection property

[Musashi]

I have a base class with some properties including a collection of Modification objects. Modification objects just hold modification data, like who modified the record, what was changed, etc.

I have another class which is like a "summary" version of the base class. Flattens some data out.
One thing I want it to do is have a Modification property that takes the latest modification from the Modification collection.

Ex.

public class BaseEntity
{
    public int PRIMARYKEY { get; set; }
    public string NAME { get; set; }
    public IList<Modification> Mods { get; set; }
}

public class BaseEntitySummary
{
    public int PRIMARYKEY { get; set; }
    public string NAME { get; set; }
    public Modification LatestMod { get; set; }
}

public class Modification
{
    public int PRIMARYKEY { get; set; }
    public int USERFK { get; set; }
    public int ModNumber { get; set; }
    public DateTime Date { get; set; }
}

I work with the BaseEntitySummary class, treating it like an entity. I have a mapping mechanism that maps BaseEntity to BaseEntitySummary, so in the end, what DataAccess sees from this:

context.BaseEntitySummarys.Where(bes => bes.LatestMod.Date < DateTime.Now)

is actually

context.BaseEntitys.Where(be => be.Mods.OrderByDescending(mod => mod.ModNumber).FirstOrDefault().Date < DateTime.Now)
    .Select(be => new BaseEntitySummary
    {
        PRIMARYKEY = be.PRIMARYKEY,
        NAME = be.NAME,
        LatestMod = be.OrderByDescending(mod => mod.ModNumber).FirstOrDefault()
    })

 

My problem lies in

context.BaseEntitys.Where(be => be.Mods.OrderByDescending(mod => mod.ModNumber).FirstOrDefault().Date < DateTime.Now)

I get a exception stating "Execution of 'System.Linq.Enumerable:FirstOrDefault(IEnumerable`1)' on the database server side currently not implemented."

 

Does anyone have any good ideas for making what I want happen? i.e. a property in an entity that takes one object from a collection property?

[Doroteya]

Hello Musashi,

The error you are experiencing is expected. Due the complexity of such a query that is related to the specifics of the scenario, we designed the API to support it with two separate calls to the database. Like this:

var mod = context.Modifications.OrderByDescending(mod => mod.ModNumber)
    .FirstOrDefault(mod => mod.Date < DateTime.Now);
var baseEntity = mod.BaseEntity;
var baseEntitySummary = new baseEntitySummary
{
    PRIMARYKEY = baseEntity.PRIMARYKEY,
    NAME = baseEntity.NAME,
    LatestMod = mod
};

Note that in order to achieve the necessary result, you need the BaseEntity navigation property in the Modification entity.

I hope this helps.



Regards,
Doroteya
Telerik