How to represent this SQL query in LINQ to entities?

Question

Hello

I'm kind of new to LINQ and I'm trying to represent the following query in LINQ to Entities:

Select * from Cotations CT1 
where CT1.CotationID = iCot_ID and 
      Revision = 
          (select max(revision) 
          from Cotations CT2 
          where CT1.CotationID = CT2.Cotation)

where iCot_ID is an external parameter, and Cotations is my entity set.

Better yet, how can I represent this as an association between two entities?

If I have the following entities:

MasterLog:

--cotationID

Cotation:

--Cotationid

--Revision key,

and for each MasterLog I want to have an association with Cotation based only on CotationID where Revision is max for that cotation (as in above query)

Thanks.

Lukasz

Answer 1

I think you have overcomplicated your query. The below should do it.

int iCot_ID  = 0;

var query = (
  from c in context.Coalation
   .Where(x=>x.Cotationid == iCot_ID)
   .OrderByDesc(x=>x.Revision)
 )
.FirstOrDefault();

---

I don't quite understand the second half of your question. Please take some time and further explain what you would like to do with MasterLog.

Here is a guess:

var cQuery = (
           from c in context.Coalation
                  group c by g.Revision into g
                  orderby g.Revision descending
           select g.First()
);

var mQuery = (
           from m in MasterLog
           join c in cQuery 
               on m.cotationID equals c.Cotationid
           where 
              m.Cotationid==iCot_ID 
           select new {
                MasterLog = m,
                Cotation = c
            }

);

Answer 2

Thank you very much for your answer. Nice solution - I didn't think of that. For the second part, I wonder if it would be possible to define an association in my model designer between MasterLog and Cotation.

MasterLog n..1 Cotation

where MasterLog.CotationID = Cotation.CotationID and Cotation.Revision = max...

The designer insists that all parts of the key be mapped, but in this case, I want n to map MasterLog.CotationID to Cotation.CotationID and Rev (which is the second field of the PK of Cotation) to the max for that cotation.