转换SQL查询在LINQ和选择计数

本文关键字：选择 LINQ SQL 查询 转换 | 更新日期: 2023-09-27 18:02:29

我有2个SQL查询，我尝试使用linqer转换，但没有结果。像这样的查询:

select distinct a.DOCID,
(select dcrea from mstdocs where DOCID = a.docid and vtype = 'table') DOC_DATE,
(select dcrea from mstdocs where DOCID= a.docid and vtype = 'read' and VTAID = '2') Read_DATE
from mstdocs a, mstdocstats b
where a.docid = b.docid
and a.vtaid = '2'
and a.vtype = 'read'
and DATEPART(mm,a.DCREA)  = '06'
and DATEPART(yyyy,a.DCREA) = '2016'
and a.docid in 
(
select distinct a.docid 
from mstdocs a, mstdocstats b
where a.docid = b.docid
and a.vtype = 'table'
and DATEPART(mm,a.DCREA)  = '06'
and DATEPART(yyyy,a.DCREA) = '2016'
)

select distinct a.docid
from mstdocs a, mstdocstats b
where a.docid = b.docid
and (select count(*) from mstdocs docs where docs.DOCID = a.docid and docs.vtaid = '2') = 1

我已经尝试将第一个查询单独转换为两个部分

  • var a =(从b在mstdocstats在a.docid = b.docid的mstdocs中加入aa.vtype.Equals("表"),,a.DCREA.Month == '06' &&a.DCREA.Year == '2016'选择a.docid) .Distinct () .ToList ();

  • var b = (from b in mstdocstats .在a.docid = b.docid的mstdocs中加入aa.Contains (a.docid)选择new {Docid = a.docid;DOC_DATE = (from mstdoc in mstdocs where .docid == mstdoc. DOC_DATE =)docid,,mstdoc。Vtype == "table" select
    mstdoc.DCREA),
    Read_DATE = (from mstdoc in mstdocs where .docid == mstdoc。docid,,mstdoc。

变成了错误，谁能告诉我错误在哪里?

转换SQL查询在LINQ和选择计数

我假设DCREA是Datetime。第一个查询首先获得mstdocs在2016年6月日期间隔内的子集，并从中获得"read"mstdocs的读子集…

var qry_1 = (from a in (
                from a in mstdocs
                join b in mstdocstats on a.docid equals b.docid 
                where a.DCREA.Month == 6 && a.DCREA.Year == 2016
                select a
            )join b in mstdocstats on a.docid equals b.docid 
             where a.vtaid == "2" && a.vtype == "read" 
             select new {
                       DOCID = a.Name,
                       Read_DATE = a.DCREA,
                       DOC_DATE = (from mstdoc in mstdocs where a.docid == mstdoc.docid && mstdoc.vtype == "table" select mstdoc.DCREA)
                     }
            ).Distinct().ToList();


var qry_2 = (from a in 
             (from a in mstdocs
              where a.vtaid == "2"
              group a by a.docid into GroupMstdocs
              where GroupMstdocs.Count() == 1
              select new { docid = GroupMstdocs.Key }
             ) join b in mstdocstats on a.docid equals b.docid
               select a.docid
            ).Distinct().ToList();
相关文章：