一句話，將Linq語句轉換為Sql語句，linqsql

一句話，將Linq語句轉換為Sql語句，linqsql


   public IEnumerable<VMB_Report> ReportView_List(VMB_ReportConditions requiredModel)
        {
          
            IEnumerable<VMB_Report> resultModel = new List<VMB_Report>();
         IQueryable<Merchant> merchantList;
              merchantList = Report_List();
          

            #region 從MerchantList中擷取VMB_Report集合

            resultModel = merchantList.Select(o => new VMB_Report
            {

                MerchantID = o.MerchantID,
                MID = o.MID,
                DBA = o.DBA,
                DBAPhone = o.DBAPhone,
                ContactName = o.ContactName,
                TotalSalesYTD = o.MerchantVolume.TotalSalesYTD,
                BankcardAvgTicketYTD = o.MerchantVolume.BankcardAvgTicketYTD,
                YTDProfit = o.MerchantVolume.YTDProfit,
                EnteredDate = o.MerchantStatus.EnteredDate,
                InstalledDate = o.MerchantStatus.InstalledDate,
                Status = o.MerchantStatus.Status,
                RegionName = o.MerchantReps.Where(a => a.LevelNumber == 1).FirstOrDefault().Rep.Region.RegionName,
                RepLev1 = (o.MerchantReps.Any(a => a.LevelNumber == 1) ? o.MerchantReps.Where(a => a.LevelNumber == 1).FirstOrDefault().Rep.Name : ""),
                RepLev2 = (o.MerchantReps.Any(a => a.LevelNumber == 2) ? o.MerchantReps.Where(a => a.LevelNumber == 2).FirstOrDefault().Rep.Name : ""),
                RepLev3 = (o.MerchantReps.Any(a => a.LevelNumber == 3) ? o.MerchantReps.Where(a => a.LevelNumber == 3).FirstOrDefault().Rep.Name : ""),
                RepLev4 = (o.MerchantReps.Any(a => a.LevelNumber == 4) ? o.MerchantReps.Where(a => a.LevelNumber == 4).FirstOrDefault().Rep.Name : ""),
                RepLev5 = (o.MerchantReps.Any(a => a.LevelNumber == 5) ? o.MerchantReps.Where(a => a.LevelNumber == 5).FirstOrDefault().Rep.Name : ""),
                Address1 = o.DBAAddress1,
                Address2 = o.DBAAddress2,
                City = o.DBACity,
                State = o.DBAState,
                Zip = o.DBAZip,
                Ps = (from op in o.Products
                     where op.Status == 100
                     select new VMPro
                     {
                         EFDe = op.EquipmentFee.Description,
                         Ptype = op.ProductType,
                         SDe = op.Software.Description
                     }).ToList()

            });
            #endregion

            Var sql=(resultModel as ObjectQuery<VMB_Report>).ToTraceString();//此句為精華，查看sql變數就是產生的sql語句。

          return resultModel;
        }

SQL語句轉化為Linq語句

分少點！回頭幫我的團隊打工償還吧！O(∩_∩)O哈哈~
-------------
我的表是Table_1s和Table_2s
linq 查詢如下：
from t in Table_1s
join f in Table_2s on t.B equals f.Key into FD
from f in FD.Where(b=>b.RowName=="B").DefaultIfEmpty()
join s in Table_2s on t.C equals s.Key into FDS
from s in FDS.Where(b=>b.RowName=="C").DefaultIfEmpty()
select new
{
A=t.A,
B=f.Value,
C=s.Value
}
---
對應sql如下：
-- Region Parameters
DECLARE @p0 NVarChar(1) SET @p0 = 'B'
DECLARE @p1 NVarChar(1) SET @p1 = 'C'
-- EndRegion
SELECT [t0].[A], [t1].[value] AS [B], [t2].[value] AS [C]
FROM [Table_1] AS [t0]
LEFT OUTER JOIN [Table_2] AS [t1] ON ([t1].[RowName] = @p0) AND ([t0].[B] = [t1].[key])
LEFT OUTER JOIN [Table_2] AS [t2] ON ([t2].[RowName] = @p1) AND ([t0].[C] = [t2].[key])
---
應該滿足你的要求！
 
教怎將SQL轉換成LINQ語句，語句如下

var q= from t in table
group t by t.document into g
select new{
g.Key,
P1=g.Max(p=>p.P1),
P2=g.Max(p=>p.P2)
}
//手寫的，請在工具裡檢查一下