将TSQL转换为Linq到实体 [英] Convert TSQL to Linq to Entities

问题描述

我有2个名为FileList和ExtensionsCategory的表：

  FileList 
 --------- -------------------------------------------------- -  
 ID路径扩展
 
 1 C：\7.pdf pdf 
 2 D：\3.png png 
 3 C：\1。 mp3 mp3 
 4 D：\32.pdf pdf 
  

和

  ExtensionsCategory 
 -------------------------- ---------------------------------- 
 ID扩展类别
 
 1 mp3多媒体
 2 pdf文件
 3 png照片
  

没有任何表格之间的关系。

现在我想知道每个类别包含多少个文件？
i在SSMS中写下以下TSQL查询，并且正常工作：

  SELECT 
类别
， SUM（FileCount）AS TotalFileCount 
 FROM 
（SELECT 
 COUNT（*）AS FileCount 
，（SELECT ExtensionsCategories.Category FROM ExtensionsCategories WHERE FileList.Extension = ExtensionsCategories.Extension）AS类别
 FROM FileList GROUP BY Extension）AS T1 GROUP BY Category 
  

我想知道有没有改进的方式来做这个没有连接？

我尝试使用Linq写实体，这是我的代码，但我无法完成它：

  var a = _dbContext.FileLists 
 .Select（fl => new 
 {
 Extension = fl。扩展，
 Count = _dbContext.FileLists.Count（），
 Cat = 
 _dbContext.ExtensionsCategories.Where（w => w.Extension == fl.Extension）.Select（s = > s。类别） 
}）。GroupBy（g => g.Extension）; 
  

解决方案

这应该给你正确的结果： -

  var result = from f in _dbContext.FileLists 
 group f by f.Extension into g 
 let firstCategory = _dbContext .ExtensionsCategories 
 .FirstOrDefault（x => x.Extension == g.Key）
选择新
 {
类别= firstCategory！= null？ firstCategory.Category：，
 TotalFileCount = g.Count（）
}; 
  

或者如果需要方法语法 -

  var result = _dbContext.FileLists.GroupBy（e => e.Extension）
 .Select（x => 
 {
 var firstCategory = _dbContext.ExtensionsCategories 
 .FirstOrDefault（z => z.Extension == x.Key）; 
 return new 
 { 
 Category = firstCategory！= null？firstCategory.Category：，
 TotalFileCount = x.Count（）
}; 
}）; 
  

小提琴与Linq对象。

更新：

如果一个类别可以包含多个扩展名，那么可以使用下面的查询： -

  var result = extensionCat.GroupBy（x => ; x.Category）
 .Select（x => 
 new 
 {
 Category = x.Key，
 TotalFileCount = fileList.Count（f => x 
 .Any（z => z.Extension == f.Extension））
} 
）; 
  

I have 2 table named FileList and ExtensionsCategory :

FileList
-------------------------------------------------------------
ID    Path         Extension

1     C:\7.pdf        pdf
2     D:\3.png        png
3     C:\1.mp3        mp3
4     D:\32.pdf       pdf

and

ExtensionsCategory 
------------------------------------------------------------
ID     Extension       Category

1        mp3           Multimedia
2        pdf           Document
3        png           Photo

there isn't any relation between tables.

now i want to know each category contain how many files ? i wrote the following TSQL query in SSMS and work fine :

SELECT 
     Category
    ,SUM(FileCount) AS TotalFileCount 
FROM    
    (SELECT 
         COUNT(*) AS FileCount
        ,(SELECT ExtensionsCategories.Category FROM ExtensionsCategories WHERE FileList.Extension = ExtensionsCategories.Extension) AS Category
    FROM FileList GROUP BY Extension) AS T1 GROUP BY Category

i want know is there any improved way to do this without Joins ?

i try to write it using Linq to Entities, this is my code but i can't complete it :

    var a = _dbContext.FileLists
        .Select(fl => new
        {
            Extension = fl.Extension,
            Count = _dbContext.FileLists.Count(),
            Cat =
                _dbContext.ExtensionsCategories.Where(w => w.Extension == fl.Extension).Select(s => s.Category)
        }).GroupBy(g => g.Extension);

解决方案

This should give you correct result:-

var result = from f in _dbContext.FileLists
             group f by f.Extension into g
             let firstCategory = _dbContext.ExtensionsCategories
                                           .FirstOrDefault(x => x.Extension == g.Key)
             select new
                    {
                        Category = firstCategory != null ? firstCategory.Category : "",
                        TotalFileCount = g.Count()
                    };

Or if you need with Method Syntax:-

var result = _dbContext.FileLists.GroupBy(e => e.Extension)
              .Select(x =>
                  {
                      var firstCategory = _dbContext.ExtensionsCategories
                                           .FirstOrDefault(z => z.Extension == x.Key);
                      return new
                        {
                           Category = firstCategory != null ? firstCategory.Category : "",
                           TotalFileCount = x.Count()
                        };
                  });

Fiddle with Linq-to-objects.

Update:

If a category can contain multiple extension then you can use below query:-

var result = extensionCat.GroupBy(x => x.Category)
                      .Select(x =>
                         new
                          {
                              Category = x.Key,
                              TotalFileCount = fileList.Count(f => x
                                                    .Any(z => z.Extension == f.Extension))
                          }
                      );

这篇关于将TSQL转换为Linq到实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！