c# Linq select join on select group by
10,379
Solution 1
Yes, Linq to SQL does have an inner join implemented:
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
Solution 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();
}
Author by
Admin
Updated on June 04, 2022Comments
-
Admin almost 2 years
I have this MS-SQL statement :
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 t.v = cv.version WHERE cv.contact_id = $ContactID(int) AND cv.isActive = 1 ORDER BY sort'
and would like to make it in linq. I did make above query divided into multiple queries witch performence is not fast. Does it exist any linq to linq joining
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 love to get all contacts with 1-2 queries not 1 query then for each contact another query...
Please help me !
-
Admin almost 13 yearsThank you it works i just changed join gm in groupMax on new { cv.mainId, cv.version } equals new { gm.mainId, gm.version } because i need to use multiple condition join... Much better then what i was useing meanwhile : ExecuteStoreQuery<ContactValue>(sqlQuery);