Linq语法I

1、Linq查询：
var re = from o in db.Order  
         join d in db.OrderDetail  
         on o.Code equals d.OrderCode  
         join p in db.Product  
         on d.ProductID equals p.id  
         where o.Code == "20150326000096"  
         select new {  
            OrderCode = o.Code,  
            ProductName = p.Name,  
            SalePrice = p.SalePrice,  
            ProductQuantity = d.Quantity,  
            Amount = d.Amount  
         }; 

等价于：
 SELECT   
    [Extent1].[ProductID] AS [ProductID],   
    [Extent1].[OrderCode] AS [OrderCode],   
    [Extent2].[Name] AS [Name],   
    [Extent2].[SalePrice] AS [SalePrice],   
    [Extent1].[Quantity] AS [Quantity],   
    [Extent1].[Amount] AS [Amount]  
    FROM  [dbo].[OrderDetails] AS [Extent1]  
    INNER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[ProductID] = [Extent2].[id]  
    WHERE ([Extent1].[OrderCode] IS NOT NULL) AND (N'20150326000096' = [Extent1].[OrderCode])

2、

lambda:
re = db.OrderDetail.Where(d => d.OrderCode == "20150326000096").Select(d => new {  
                    OrderCode = d.Order.Code,  
                    ProductName = d.Product.Name,  
                    SalePrice = d.Product.SalePrice,  
                    ProductQuantity = d.Quantity,  
                    Amount = d.Amount                 
                });  

等价于：(N 在这里表示(nvarchar)Unicode)

3、

公用表达式：
with tmp as(
    select userid,score from gamescoreinfo gsi where score<1000
)
update gamescoreinfo set score=t.score+100
    from gamescoreinfo gsi
    inner join tmp t on gsi.userid=t.userid


方法一：
select p.ID,p.ProjectNO,p.ProjectName,p.ContractNumber,c.ContactStates from LbtProjectInfo p  join ContractRview c  on c.xmbh=p.ContractNumber where c.ContactStates='2'  and p.ProjectName not in(select d.ProjectName  from LabsFlow d);

-----------------
方法二：(效果更好)
with cr
as
(
select d.ProjectName  from LabsFlow d
)
select p.ID,p.ProjectNO,p.ProjectName,p.ContractNumber,c.ContactStates from LbtProjectInfo p 
inner  join ContractRview c  on c.xmbh=p.ContractNumber where c.ContactStates='2'  
and p.ProjectName not in(select  e.ProjectName  from cr e)


=================
临时表 TP
虚拟表pj
select * into TP from (select p.ID,p.ProjectNO,p.ProjectName,p.ContractNumber,c.ContactStates from LbtProjectInfo p 
inner  join ContractRview c  on c.xmbh=p.ContractNumber where c.ContactStates='2')pj


=========================
public static DataTable PageView(string tbname, string FieldKey, int PageCurrent, int PageSize, string FieldShow, string Where, string FieldOrder, ref int RecordCount)  
    {  
        SqlParameter[] parms = new SqlParameter[]   
        {   
            new SqlParameter("@tbname",SqlDbType.VarChar),   
            new SqlParameter("@FieldKey",SqlDbType.VarChar,1000),  
            new SqlParameter("@PageCurrent",SqlDbType.Int),  
            new SqlParameter("@PageSize",SqlDbType.Int),  
            new SqlParameter("@FieldShow",SqlDbType.VarChar,1000),  
            new SqlParameter("@Where",SqlDbType.VarChar,1000),  
            new SqlParameter("@FieldOrder",SqlDbType.VarChar,1000),  
            new SqlParameter("@PageCount",SqlDbType.Int),  
            new SqlParameter("@RecordCount",SqlDbType.Int),  
  
  
        };  
        parms[0].Value = tbname;  
        parms[1].Value = FieldKey;  
        parms[2].Value = PageCurrent;  
        parms[3].Value = PageSize;  
        parms[4].Value = FieldShow;  
        parms[5].Value = Where;  
        parms[6].Value = FieldOrder;  
        parms[7].Direction = ParameterDirection.Output;  
        parms[8].Direction = ParameterDirection.Output;  
  
  
        DataTable dt = new DataTable();  
        SQLHelper.FillDataTablePage("PageView", dt, parms);  
        RecordCount = Convert.ToInt32(parms[8].Value);  
        return dt;  
  
  
    }