Linq两表联查结果集处理

全局定义一个同结果集类型的变量：

var strQuery =(new int[] { 1 }).Select(x => new { DispLevel = "empty",
                    Separator ="empty",
                    GrpShortCut = "empty" ,
                    AddToolBar ="empty",
                    FuncName ="empty",
                    FuncDesc ="empty",
                    FuncTypeFlag ="empty",
                    FuncGroup ="empty",
                    AssemblyFile="empty",
                    AssemblyName="empty",
                    ShortCut="empty",
                    IconIndex=Convert.ToDecimal(0)
                }).ToList();

局部查询的结果集：

strQuery = ctx.Secgrpfuncrel.Where(t => t.Factory == sFactory && t.ProgramId == sProgramId && t.SecGrpId == sSecGrpId && t.DispLevel.CompareTo(sDispLevel) >= 0).Join(
                        ctx.Secfunction,
                        a => new { f = a.FuncName },
                        b => new { f = b.FuncName },
                        (a, b) => new
                        {
                            a.DispLevel,
                            a.Separator,
                            GrpShortCut = a.ShortCut,
                            a.AddToolBar,
                            b.FuncName,
                            b.FuncDesc,
                            b.FuncTypeFlag,
                            b.FuncGroup,
                            b.AssemblyFile,
                            b.AssemblyName,
                            b.ShortCut,
                            b.IconIndex,
                        }
                        ).OrderBy(t => t.FuncName).ToList();

注：

1、两表相同字段，其中一个取别名（GrpShortCut = a.ShortCut,别名=[字段名]）

2、注意看报错信息，变量字段类型不同会导致类型转换失败。

==============

两表联查结果集为a.*时（两种处理方法）：

SQL:

strQuery = "SELECT A.*"
                                 + " FROM SPCEDCDATA A, SPCCALDATA B"
                                 + " WHERE A.FACTORY=?"
                                 + " AND A.FACTORY = B.FACTORY"
                                 + " AND A.CHART_ID = B.CHART_ID"
                                 + " AND A.HIST_SEQ = B.HIST_SEQ"
                                 + " AND A.UNIT_SEQ = B.UNIT_SEQ"
                                 + " AND B.EXCLUDE_FLAG<>'Y'"
                                 + " AND A.CHART_ID=?"
                                 + " AND (A.HIST_SEQ > ? OR (A.HIST_SEQ = ?	AND A.UNIT_SEQ > ?)" + " OR (A.HIST_SEQ = ? AND A.UNIT_SEQ = ? AND A.VALUE_SEQ >= ?))"
                                 + " AND A.TRAN_TIME<=?"
                                 + " AND A.TRAN_TIME>=?"
                                 + " ORDER BY A.HIST_SEQ ASC, A.UNIT_SEQ ASC, A.VALUE_SEQ ASC";

 to Linq：

var listSpcedcdata = (new int[] { 1 }).Select(x=>new {
                Factory   ="empty",
                ChartId   = "empty",
                HistSeq   = Convert.ToDecimal(0),
                TranTime  = "empty",
                LotResFlag = "empty",
                LotId     = "empty",
                MatId     = "empty",
                MatVer    = Convert.ToDecimal(0),
                Flow      = "empty",
                Oper      = "empty",
                ProcOper  = "empty",
                ResId     = "empty",
                ProcResId = "empty",
                EventId   = "empty",
                CharId    = "empty",
                UnitId    = "empty",
                UnitSeq   = Convert.ToDecimal(0),
                ValueType = "empty",
                ValueSeq  = Convert.ToDecimal(0),
                ValueCount = Convert.ToDecimal(0),
                Value1     = "empty",
                Value2     ="empty",
                Value3     ="empty",
                Value4     ="empty",
                Value5     ="empty",
                Value6     ="empty",
                Value7     ="empty",
                Value8     ="empty",
                Value9     = "empty",
                Value10     ="empty",
                Value11     ="empty",
                Value12     ="empty",
                Value13     ="empty",
                Value14     ="empty",
                Value15     ="empty",
                Value16     ="empty",
                Value17     ="empty",
                Value18     ="empty",
                Value19     ="empty",
                Value20     ="empty",
                Value21     ="empty",
                Value22     ="empty",
                Value23     ="empty",
                Value24     ="empty",
                Value25     ="empty",
                Nominal     = "empty",
                ProcessSigma = "empty",
                EdcComment   ="empty",
                EdcHistSeq   = Convert.ToDecimal(0),
                EdcCharSeq   = Convert.ToDecimal(0),
                EdcUnitSeq   =Convert.ToDecimal(0),
                CreateUserId = "empty",
                CreateTime   = "empty",
                UpdateUserId = "empty",
                UpdateTime   ="empty",
                ResvField1   ="empty",
                ResvField2   ="empty",
                ResvField3   ="empty",
                ResvField4   ="empty",
                ResvField5   = "empty",
            }).ToList();

 

listSpcedcdata = ctx.Spcedcdata.Where(t =>t.Factory==sFactory&& t.ChartId == sChartId && (t.HistSeq > iHistSeq || (t.HistSeq == iHistSeq && t.UnitSeq > iUnitSeq) || (t.HistSeq == iHistSeq && t.UnitSeq == iUnitSeq
                && t.ValueSeq >= iValueSeq)) && t.TranTime.CompareTo(sToTime) <= 0 && t.TranTime.CompareTo(sFromTime) >= 0)
                    .Join(
                    ctx.Spccaldata.Where(t=>t.ExcludeFlag!="Y"),
                    a => new { f=a.Factory,id=a.ChartId,hs=a.HistSeq,us=a.UnitSeq},
                    b => new { f = b.Factory, id = b.ChartId, hs = b.HistSeq, us = b.UnitSeq },
                    (a, b) => new 
                    {
                        a.Factory , 
                        a.ChartId ,
                        a.HistSeq ,
                        a.TranTime ,
                        a.LotResFlag ,
                        a.LotId ,
                        a.MatId ,
                        a.MatVer ,
                        a.Flow ,
                        a.Oper ,
                        a.ProcOper ,
                        a.ResId ,
                        a.ProcResId ,
                        a.EventId ,
                        a.CharId ,
                        a.UnitId ,
                        a.UnitSeq ,
                        a.ValueType ,
                        a.ValueSeq ,
                        a.ValueCount ,
                        a.Value1 ,
                        a.Value2 ,
                        a.Value3 ,
                        a.Value4 ,
                        a.Value5 ,
                        a.Value6 ,
                        a.Value7 ,
                        a.Value8 ,
                        a.Value9 ,
                        a.Value10,
                        a.Value11,
                        a.Value12,
                        a.Value13,
                        a.Value14,
                        a.Value15,
                        a.Value16,
                        a.Value17,
                        a.Value18,
                        a.Value19,
                        a.Value20,
                        a.Value21,
                        a.Value22,
                        a.Value23,
                        a.Value24,
                        a.Value25,
                        a.Nominal,
                        a.ProcessSigma,
                        a.EdcComment  ,
                        a.EdcHistSeq  ,
                        a.EdcCharSeq  ,
                        a.EdcUnitSeq  ,
                        a.CreateUserId,
                        a.CreateTime  ,
                        a.UpdateUserId,
                        a.UpdateTime  ,
                        a.ResvField1  ,
                        a.ResvField2  ,
                        a.ResvField3  ,
                        a.ResvField4  ,
                        a.ResvField5  ,

                    } ).OrderBy(t=>t.HistSeq).ThenBy(t=>t.UnitSeq).ThenBy(t=>t.ValueSeq).ToList();

（2）

Spcedcdata c = new Spcedcdata();
var listSpcedcdata = (new int[] { 1 }).Select(x=>new {
                a=c
            }).ToList();

注意变量名不要是a，b      避免跟Linq查询中的a，b冲突。

listSpcedcdata = ctx.Spcedcdata.Where(t =>t.Factory==sFactory&& t.ChartId == sChartId && (t.HistSeq > iHistSeq || (t.HistSeq == iHistSeq && t.UnitSeq > iUnitSeq) || (t.HistSeq == iHistSeq && t.UnitSeq == iUnitSeq
                && t.ValueSeq >= iValueSeq)) && t.TranTime.CompareTo(sToTime) <= 0 && t.TranTime.CompareTo(sFromTime) >= 0)
                    .Join(
                    ctx.Spccaldata.Where(t=>t.ExcludeFlag!="Y"),
                    a => new { f=a.Factory,id=a.ChartId,hs=a.HistSeq,us=a.UnitSeq},
                    b => new { f = b.Factory, id = b.ChartId, hs = b.HistSeq, us = b.UnitSeq },
                    (a, b) => new 
                    {
                        a
                    } ).OrderBy(t=>t.a.HistSeq).ThenBy(t=>t.a.UnitSeq).ThenBy(t=>t.a.ValueSeq).ToList();

取值： 

View_EDC_Data_Out.unit_list[index].factory= strQery[index].a.Factory;

 

转载于:https://my.oschina.net/8824/blog/3066203