linq 分组与统计 , not in/in功能

一. SqlCommand用sql语句实现与linq实现方式对比(in与notin)
注：not in 在linq里面:from a in db.ContactTable where !(...)..

1.SqlCommand语句实现多层in 

using (iVASDatabase db = new iVASDatabase()) { SqlCommand cmd = new SqlCommand(); string sql = @" Select Email from tblContact where ContactNO in (Select top 1 TGOBillingContact from tblTGOLocation where Code in (select LocationCode from tblTGOContractServices where CustomerNo_ = @CustomerNo))"; cmd.CommandText = sql; SqlParameter param = new SqlParameter("@CustomerNo", strCustomerNo); param.DbType = System.Data.DbType.Double; cmd.Parameters.Add(param); cmd.Connection = db.Connection as SqlConnection; if (db.Connection.State != System.Data.ConnectionState.Open) { db.Connection.Open(); } object objScalar = cmd.ExecuteScalar(); return objScalar == null ? null : objScalar.ToString(); }

2.用linq 实现sql的多层in

using (iVASDatabase db = new iVASDatabase()) { var query = from a in db.ContactTable where ( from b in db.TGOLocationTable where ( from c in db.TGOContractServicesTable where c.CustomerNo_ == double.Parse(strCustomerNo) select c.LocationCode ).Contains(b.Code) select b.TGOBillingContact ).Contains(a.ContactNO) select a.Email; List<string> listEmail = query.ToList(); return !listEmail.Any() ? null : listEmail[0]; }

query生成sql语句：

{SELECT [t0].[Email] FROM [dbo].[tblContact] AS [t0] WHERE EXISTS( SELECT NULL AS [EMPTY] FROM [dbo].[tblTGOLocation] AS [t1] WHERE ([t1].[TGOBillingContact] = [t0].[ContactNO]) AND (EXISTS( SELECT NULL AS [EMPTY] FROM [dbo].[tblTGOContractServices] AS [t2] WHERE ([t2].[LocationCode] = [t1].[Code]) AND ([t2].[CustomerNo_] = 205823) )) ) }

 

二.linq分组与统计

var query = from a in db.ThinkTelCSVTable from b in db.ThinkTelCDRTable where a.CSVID == b.CSVID && b.Field4 < b.Field1 && b.CSVDate >= startDate group b by new { a.CSVDate, a.CSVName } into g orderby g.Key.CSVDate descending select new { CSVDate = g.Key.CSVDate, CSVName = g.Key.CSVName, ExceptionCount = g.Count(), TeraGoCost = g.Sum(t => t.Field1), ClientCost = g.Sum(t => t.Field4) };

query生成的sql语句：

{SELECT [t2].[CSVDate], [t2].[CSVName], [t2].[value] AS [ExceptionCount], [t2].[value2] AS [TeraGoCost], [t2].[value3] AS [ClientCost] FROM ( SELECT COUNT(*) AS [value], SUM([t1].[Field1]) AS [value2], SUM([t1].[Field4]) AS [value3], [t0].[CSVDate], [t0].[CSVName] FROM [dbo].[tblThinkTelCSV] AS [t0], [dbo].[tblThinkTelCDR] AS [t1] WHERE ([t0].[CSVID] = [t1].[CSVID]) AND ([t1].[Field4] < [t1].[Field1]) AND ([t1].[CSVDate] >= @p0) GROUP BY [t0].[CSVDate], [t0].[CSVName] ) AS [t2] ORDER BY [t2].[CSVDate] DESC