C # Linq select join in select group

Question

I have this MS-SQL expression:

SELECT cv.id FROM ContactValue cv INNER JOIN ( SELECT mainId, max(version) as v FROM ContactValue WHERE version <= $Version(int) GROUP BY mainId ) AS t ON t.mainId = cv.mainId AND tv = cv.version WHERE cv.contact_id = $ContactID(int) AND cv.isActive = 1 ORDER BY sort' 

and would like to do it in linq. I made the request above, divided into several requests, witchiness is not fast. Is there any linq for linq connection

My C # code:

  var groupMax = from cv in db.ContactValue where cv.contact_id == ContactID && cv.version <= Version orderby cv.sort group cv by cv.mainId into gcv select new { mainID = gcv.Key, version = gcv.Max(cv => cv.version) }; foreach (var data in groupMax.ToList()) { var Query = from cv in db.ContactValue where cv.contact_id == ContactID && cv.mainId == data.mainID && cv.version == data.version && cv.isActive == true select cv; if (Query.Count() > 0) { ContactValue tmp = Query.First(); } } 

I would like to receive all contacts with 1-2 requests, and not 1 request, then for each contact a different request ...

Please help me!

Answer 1

Yes, Linq to SQL has a built-in connection:

 var groupMax = from cv in db.ContactValue where cv.contact_id == ContactID && cv.version <= Version orderby cv.sort group cv by cv.mainId into gcv select new { mainID = gcv.Key, version = gcv.Max(cv => cv.version) }; var res = from cv in db.ContactValue join gm in groupMax on cv.version equals gm.version where cv.contact_id == ContactID && cv.isActive orderby cv.version ascending /*for example*/ select cv 

Answer 2

protected void rptPriceRachiveBind () {

  using (MyEntities ctx = new MyEntities()) { var catRef = Convert.ToInt32(Request.QueryString["CategoryRef"]); var prodCounts = ( from A in ctx.Products join B in ctx.ProductPrices on A.ProductId equals B.ProductRef where A.CategoryRef == catRef group A by new { A.Name,B.ProductRef } into catGp select new { catGp.Key.ProductRef, catGp.Key.Name, proIdCount = catGp.Count() }).ToList(); Repeater1.DataSource = prodCounts; Repeater1.DataBind(); }