如何在实体框架中使用条件聚合SQL查询? [英] How to use conditional aggregation sql query in Entity Framework?

问题描述

我正在使用具有LINQ扩展方法语法的Asp.Net MVC 5和Entity Framework 6.2.0.

I am using Asp.Net MVC 5 and Entity Framework 6.2.0 with LINQ Extension Methods syntax.

我有5个变量表，详细信息如下:

I have 5 tables of variants, details below:

产品表:

ProductID Name
    12    T-Shirt

变量表:

VariantID  ProductID  Name
    1         12      Size
    2         12      Color
    3         12      Material

VariantOption表:

VariantOption Table:

VariantOptionID  VariantID  VariantOptionName
      1              1            Small
      2              1            Medium
      3              2            Red
      4              2            Blue
      5              3            Cotton
      6              3            Lawn

Sku表:

SkuID  ProductID  SKU              Price   Barcode
  1       12      Th-Sm-Red-Cot    120.00  345423
  2       12      Th-Sm-Red-Lon    130.00  345454
  3       12      Th-Sm-Blue-Cot   140.00  345451
  4       12      Th-Sm-Blue-Lon   150.00  345431
  5       12      Th-Md-Red-Cot    160.00  345472
  6       12      Th-Md-Red-Lon    170.00  345479
  7       12      Th-Md-Blue-Cot   180.00  654353
  8       12      Th-Md-Blue-Lon   190.00  254353

VariantOptionCombination表:

VariantOptionCombination Table:

VariantOptionID  SkuID
      1            1
      3            1
      5            1
      1            2
      3            2
      6            2
      1            3
      4            3
      5            3
      1            4
      4            4
      6            4

我想将这些表记录显示为网页上的

I want to show these tables records on web page as.

Size    Color  Material  Price   Sku
Small   Red    Cotton    120.00  345423
Small   Red    Lawn      130.00  345454
Small   Blue   Cotton    140.00  345451
Small   Blue   Lawn      150.00  345431
Medium  Red    Cotton    160.00  345472
Medium  Red    Lawn      170.00  345479
Medium  Blue   Cotton    180.00  654353
Medium  Blue   Lawn      190.00  254353

我正在使用此查询来实现所需的输出.如何在Entity Framework linq中翻译此查询?

I am using this query to achieve the desire output. How can i translate this query in Entity Framework linq?

select  max(case when v.Name = 'Size' then vo.Name end) as Size,
    max(case when v.Name = 'Color' then vo.Name end) as Color,
    max(case when v.Name = 'Material' then vo.Name end) as Material,
    s.price
from ProductSKU s
join ProductVariantOptionCombination voc
on s.SkuID = voc.SkuId
join ProductVariantOption vo
on vo.VariantOptionId = voc.VariantOptionId  
join ProductVariant v
on v.VariantId = vo.VariantId
group by s.Price;

推荐答案

尝试这样；

        var query = from s in YourDbContext.DbSet<ProductSKU>
            join voc in YourDbContext.DbSet<ProductVariantOptionCombination> on s.SkuID equals voc.SkuID
            join vo in YourDbContext.DbSet<ProductVariantOption> on voc.VariantOptionID equals vo.VariantOptionID
            join v in YourDbContext.DbSet<ProductVariant> on vo.VariantID equals v.VariantID
            group new {s,voc, vo, v} by s.Price
            into g
            select new
            {
                Price = g.Key,
                Size = g.Max(x => x.v.Name == "Size" ? x.vo.VariantOptionName : ""),
                Color = g.Max(x => x.v.Name == "Color" ? x.vo.VariantOptionName : ""),
                Material = g.Max(x => x.v.Name == "Material" ? x.vo.VariantOptionName : "")
            };

这篇关于如何在实体框架中使用条件聚合SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！