Linq查询与表连接，case语句，计数，group by子句

问题描述

我想运行一个linq查询，它将返回值到我的自定义DTO.这个特定的linq查询将需要考虑使用切换案例语句，计数(*)和分组依据

I want to run a linq query that will return values to my custom DTO. This particular linq query will need to take into account joins from multiple tables, using switch case statements, count (*) and group by

这是查询的SQL版本示例，我将需要等价于...的LinQ.

This is the SQL version sample of the query I will need a LinQ equivalent of...

select
slm.SLType,
count(c.EStatID) as EStat,
COUNT(cpd.TrId) as Training,
COUNT(
CASE WHEN cpd.TrStat=  44 THEN  1
     WHEN cpd.TrStat!=  44 THEN NULL
     WHEN cpd.TrStat IS NULL  THEN  NULL
END) as TrainingComplete,
COUNT(
CASE WHEN cpd.CndAssess =  44 THEN  1
     WHEN cpd.CndAssess !=  44 THEN NULL
     WHEN cpd.CndAssess IS NULL  THEN  NULL
END) as AssessmentComplete
from TabC c , TabCPD cpd, TabSLM slm
where cpd.SLid = slm.SLid
and c.Id= cpd.CID
and c.O_Id = 1
group by slm.SLType

它以以下格式返回记录.我将每条记录作为新行，用逗号分隔字段.以下数字仅作为示例

It returns records in the following format. I have put each record as a new line with fields separated by commas. The numbers below are just as an example

TypeA, 0 , 1 , 1, 0
TypeB, 1 , 0 , 1, 0

我正在尝试使用以下格式创建一个linq查询，但运气不佳

I am trying to create a linq query in the format like the one below without much luck

var query =
    from c in TabC, ......
    select new MyCustomTableC_DTO
    {
      DTOproperty = c.MatchingTable property,....
    }

MyCustomTableC_DTO将为查询中的每个字段都具有一个属性.任何想法如何做到这一点?我将用来构建MyCustomTableC_DTO类型的列表的查询

MyCustomTableC_DTO will have a property for each field in the query. Any idea how to accomplish this? The query I will use to build a list of type MyCustomTableC_DTO

感谢您的时间...

推荐答案

当您尝试将该SQL语句逐行转换为LINQ时，您将得到如下内容:

When you try to convert that SQL statement to LINQ line by line, you would get something like this:

from row in (
    from c in db.TabC
    from cpd in db.TabPD
    from slm in db.TabSLM
    where cpd.SLid == slm.SLid
    where c.Id == cpd.CID
    where c.O_Id == 1
    select new { c, cpd, slm })
group row in row.slm.SLType into g
select new
{
    SLType = g.Key,
    EStat = g.Count(r => r.c.EstatID != null),
    Training = g.Count(r => r.cpd.TrId != null),
    TrainingComplete =
        g.Count(r => r.cpd.TrStat == 44),
    AssessmentComplete =
        g.Count(r => r.cpd.CndAssess == 44)
};

但是，此查询使事情复杂化，并且完全忽略了这样一个事实，即Entity Framework对模型了解更多，并生成所有外键作为实体上的属性.除此之外，使用LINQ，您通常必须采用相反的方法.例如，在您的情况下，不要以TabC或TabSLM实体开头，而是以TabPD开头，因为该表是交叉表.有了这些知识，我们可以这样编写LINQ查询:

This query however, over complicates things and completely ignores that fact that Entity Framework knows much more about the model and generates all foreign keys as properties on the entities. Besides that, with LINQ, you must often approach things the other way around. In your case for instance, don't start with the TabC or TabSLM entity, but TabPD, since that table is the cross-table. With this knowledge, we can write the LINQ query like this:

from cpd in db.TabCPDs
where cpd.TabC.O_Id == 1
group cpd by cpd.TabSLM.SLType into g
select new
{
    SLType = g.Key,
    EStat = g.Count(r => r.TabC.EstatID != null),
    Training = g.Count(r => r.TrId != null),
    TrainingComplete =
        g.Count(r => r.TrStat == 44),
    AssessmentComplete =
        g.Count(r => r.CndAssess == 44)
};

这要简单得多，而且(如果我没有记错的话)具有相同的结果.

This is much simpler and (if I'm not mistaking) has the same result.

这篇关于Linq查询与表连接，case语句，计数，group by子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！