我可以在linq到sql C#中使用聚合函数进行联接和子查询吗

本文关键字：函数 查询 linq sql 我可以 | 更新日期: 2023-09-27 18:20:10

目前我的sql语句运行良好，但我需要将其移动到linq语句

select id_matter, COUNT(id_matter) 
from Matter m 
left join ClavT cl1 
on cl1.matter_id = m.id_matter
where m.plan_id = 12 and

    cl1.valuex in (
        select valuex from ClavT c1
        where c1.matter_id = 35
    )
group by id_matter
order by COUNT(id_matter) desc

现在我有

var innerQuery = from c in context.ClavT where c.matter_id == matter.id_matter select c.valuex;
var query = from m in context.Matter
                join c in context.ClavT on m.id_matter equals c.matter_id into mc
                where innerQuery.Contains(c)
                select m;

但是我在query:中看不到c

Error   19  The name 'c' does not exist in the current context

我仍然错过了聚合

我想返回一个带有实体和这些像一样的计数的对象

     select new
        {
             m
            ,count(m) // maybe???
        }
     ).ToList();

谢谢！

我可以在linq到sql C#中使用聚合函数进行联接和子查询吗

是否需要内部查询？你就不能有吗

select id_matter, COUNT(id_matter) 
from Matter m 
left join ClavT cl1 
on cl1.matter_id = m.id_matter
where m.plan_id = 12 and c1.matter_id = 35
group by id_matter
order by COUNT(id_matter) desc

和

var query = from m in context.Matter
            join c in context.ClavT 
              on m.id_matter equals c.matter_id into mc
            from c in mc.DefaultIfEmpty() //Left Join
            where m.plan_id == 12 && c.matter_id == 35
            select m;
var mattercount = query.Count();
相关文章：