SQL子查询+计数(在一列上不同)的LINQ lambda语法

Question

如何在lambda-notation LINQ中编写以下SQL查询?

select 'myString', count(distinct val)
    from myChildTable
    where mytable_id in (
        select id
            from myTable
            where col1_int = 6
            and col2_int between 1 and 366
            and col3_str = 'myString'
        )

作为参考，下面的两个表是:

create table myTable (
id int primary key identity,
col1_int int not null,
col2_int int not null,
col3_str varchar(1024)
)
create table myChildTable (
id int primary key identity,
mytable_id int not null foreign key references myTable(id),
val varbinary(13) not null
)

Answer 1

这可以缩短，但我发现如果分开阅读更容易:

// list ids from table
var ids = myTable.Where(x => x.Col1Int == 6 && x.Col2Int >= 1 && x.Col2Int <= 366 && x.Col3Str == "myString").Select(x => x.Id).ToList();
// use in subquery for values (non-distinct)
var vals = myChildTable.Where(x => ids.Contains(x.Id)).Select(x => x.Val).ToList();
// project to distinct dictionary of value, count
var dict = vals.Distinct().ToDictionary(x => x, x => vals.Count(y => y == x));

使用这样的类似乎可以工作:

class ParentEntity
{
    public int Id { get; set; }
    public int Col1Int { get; set; }
    public int Col2Int { get; set; }
    public string Col3Str { get; set; }
    public ParentEntity(int id, int col1Int, int col2Int, string col3Str)
    {
        Id = id;
        Col1Int = col1Int;
        Col2Int = col2Int;
        Col3Str = col3Str;
    }
}
class ChildEntity
{
    public int Id { get; set; }
    public int ParentEntityId { get; set; }
    public string Val { get; set; }
    public ChildEntity(int id, int parentEntityId, string val)
    {
        Id = id;
        ParentEntityId = parentEntityId;
        Val = val;
    }
}

Answer 2

这个查询应该是:

from c in MyChildTables
where MyTables.Any (mt => mt.Col1_int == 6 &&  mt.Col2_int >= 1 && 
      mt.Col2_int <= 366 && mt.Col3_str == "myString" && mt.Id == c.Id)
group c by true into g
select new
{
    Col1 = "myString",
    Col2 = g.Select (x => x.Val).Distinct().Count ()
}

或者如果你想用lambda表示法:

MyChildTables
   .Where (
      c => 
         MyTables
            .Any (
               mt => 
                     (((((mt.Col1_int == 6) && (mt.Col2_int >= 1)) && (mt.Col2_int <= 366)) && (mt.Col3_str == "myString")) && (mt.Id == c.Id))
            )
   )
   .GroupBy (c => True)
   .Select (
      g => 
         new  
         {
            Col1 = "myString", 
            Col2 = g.Select (x => x.Val).Distinct ().Count ()
         }
   )