时间戳

首页 > 解决方案 > 将 SQL 查询转换为 Linq to Entities

wpf - 将 SQL 查询转换为 Linq to Entities

问题描述

您好，这是一个 SQL 查询：

select FICHES_ARTICLES.ART_CODE,ART_LIBELLE1,SUM(det_pnet) as PoidsNet
from STOCK_ENT,STOCK_DET,FICHES_ARTICLES,CLIENTS,MVTS_SEQUENCE
where STOCK_ENT.ENT_ID=STOCK_DET.ENT_ID
and STOCK_ENT.ENT_PROP=CLIENTS.CLI_CODE
and STOCK_ENT.ART_CODE=FICHES_ARTICLES.ART_CODE
and STOCK_ENT.ENT_ID=MVTS_SEQUENCE.ENT_ID
and SEQ_STATUT<>'V'
and CLI_CODE='0030000'
group by ART_LIBELLE1,FICHES_ARTICLES.ART_CODE
having SUM(det_pnet)<2000
order by ART_LIBELLE1;
go

我想在 WPF 应用程序中使用 Linq to Entities 编写相同的内容。所以这就是我所拥有的：

private void GetDatas()
    {
        using(GSUITEEntities dc=new GSUITEEntities())
        {
            try
            {
                var query = from ent in dc.STOCK_ENT
                            join det in dc.STOCK_DET on ent.ENT_ID equals det.ENT_ID
                            join art in dc.FICHES_ARTICLES on ent.ART_CODE equals art.ART_CODE
                            join cli in dc.CLIENTS on ent.ENT_PROP equals cli.CLI_CODE
                            join seq in dc.MVTS_SEQUENCE on ent.ENT_ID equals seq.ENT_ID
                            where seq.SEQ_STATUT != "V" && cli.CLI_CODE == "0030000"
                            group new { ent, det, art, cli, seq } by new
                            {
                                art.ART_CODE,
                                art.ART_LIBELLE1
                            } into grouped
                            orderby grouped.Key.ART_LIBELLE1
                            select new
                            {
                                code=grouped.Key.ART_CODE,
                                lib=grouped.Key.ART_LIBELLE1,
                                pnet=grouped.Sum(x=>x.det.DET_PNET)
                            };
            }
            catch (Exception)
            {

                throw;
            }
        }
    }

我无法翻译的是Having SUM(det_pnet)SQL 查询的一部分。有人可以让我朝着正确的方向前进吗？

谢谢

标签： wpfentity-frameworklinq

解决方案

尝试使用let 子句：

var query = from ent in dc.STOCK_ENT
            join det in dc.STOCK_DET on ent.ENT_ID equals det.ENT_ID
            join art in dc.FICHES_ARTICLES on ent.ART_CODE equals art.ART_CODE
            join cli in dc.CLIENTS on ent.ENT_PROP equals cli.CLI_CODE
            join seq in dc.MVTS_SEQUENCE on ent.ENT_ID equals seq.ENT_ID
            where seq.SEQ_STATUT != "V" && cli.CLI_CODE == "0030000"
            group new { ent, det, art, cli, seq } by new
            {
                art.ART_CODE,
                art.ART_LIBELLE1
            } into grouped
            let sum = grouped.Sum(x => x.det.DET_PNET)
            where sum > 2000
            orderby grouped.Key.ART_LIBELLE1
            select new
            {
                code = grouped.Key.ART_CODE,
                lib = grouped.Key.ART_LIBELLE1,
                pnet = sum
            };

推荐阅读