Entity Framework: join method

Question

Hi,

I spend enough time already so I need help from stackoverflow.

Here is an entity

Entity: Asset 
Field: AssetVersionID (int) PK
Field: AssetID (int) FK
Field: ReleaseTimestamp (DateTime)

My task is to select assets with unique AssetID and with latest ReleaseTimestamp. basically I need function like this

public IQueryable<Asset> GetAssets(List<int> assetIds)

in SQL it looks like this:

select a1.* 
from Assets as a1
join (
        select t.AssetID, max(t.ReleaseTimestamp) as ReleaseTimestamp
        from Asssets as t
        where AssetID in (1,2,3,4,5)
        goup by AssetID
    ) as a2
on a1.AssetID = a2.AssetID and a1.ReleaseTimestamp= a2.ReleaseTimestamp

how to do it with EF and LINQ ?

I tried this:

public IQueryable<Asset> GetAssets(List<int> assetUIds)
{
 IQueryable<Asset> assets = _context.Assets.Where(a => assetUIds.Contains(a.UID));
 var ff = assets.GroupBy(a => a.UID, a => a,
              (uid, p2) => new {KEY = uid, rel = p2.Max(p2a => p2a.ReleaseTimestamp)});
 IQueryable<Asset> assets2 = _context.Assets;

 var assets3 = from ass1 in assets2
          join ass2 in ff on new {ass1.UID, ass1.ReleaseTimestamp} equals new {ass2.KEY, ass2.rel}
          select ass1;
 return assets3;
}

but i have syntax error near "join" basically this works:

join ass2 in ff on ass1.UID equals ass2.KEY

and this does not

join ass2 in ff on new {ass1.UID, ass1.ReleaseTimestamp} equals new {ass2.KEY, ass2.rel}

as always :)

Please Help !

Answer 1

The expressions that you're joining need to be identical. Change it to:

join ass2 in ff on new {KEY = ass1.UID, rel = ass1.ReleaseTimestamp} equals new {ass2.KEY, ass2.rel}