复杂Linq语句写法

从网上收藏的复杂Linq语句写法

 

1、case when：

原型：

sql原型：

SELECT ProductID, Name, Color,

    CASE

      WHEN Color = 'Red' THEN StandardCost

      WHEN Color = 'Black' THEN StandardCost + 10

      ELSE ListPrice   

    END Price

FROM SalesLT.Product

Linq代码：

Products.Select(P => new

{

  ID = P.ProductID,

  Name = P.Name,

  Color = P.Color,

  Price = (P.Color == "Red" ? P.StandardCost : (P.Color == "Black" ? P.StandardCost + 10 : P.ListPrice))

});

 

2、where中使用case when

linq代码：

Products

.Where(P => (P.Color == "Red" ? (P.StandardCost > 100) : (P.Color == "Black" ? P.ListPrice > 100 : P.ListPrice == P.StandardCost)))

.Select(P => new

{

  ID = P.ProductID,

  Name = P.Name,

  Color = P.Color,

  StandardCost = P.StandardCost,

  ListPrice = P.ListPrice

});

sql原型：

SELECT ProductID, Name, Color, StandardCost, ListPrice

FROM SalesLT.Product

WHERE (

  (CASE

    WHEN Color = 'Red' THEN

      (CASE

        WHEN StandardCost > 100 THEN 1

        WHEN NOT (StandardCost > 100) THEN 0

        ELSE NULL

       END)

    WHEN Color = 'Black' THEN

      (CASE

        WHEN ListPrice > 100 THEN 1

        WHEN NOT (ListPrice > 100) THEN 0

        ELSE NULL

       END)

    ELSE

      (CASE

        WHEN ListPrice = StandardCost THEN 1

        WHEN NOT (ListPrice = StandardCost) THEN 0

        ELSE NULL

       END)

   END)) = 1

3、group by中使用case when

linq代码：

Products.GroupBy(P => P.StandardCost > 1000? P.Color : P.SellStartDate.ToString())

 

sql原型：

-- Region Parameters

DECLARE @p0 Decimal(8,4) = 1000

-- EndRegion

SELECT [t1].[value] AS [Key]

FROM (

  SELECT

    (CASE

      WHEN [t0].[StandardCost] > @p0 THEN CONVERT(NVarChar(MAX),[t0].[Color])

      ELSE CONVERT(NVarChar(MAX),[t0].[SellStartDate])

     END) AS [value]

  FROM [SalesLT].[Product] AS [t0]

  ) AS [t1]

GROUP BY [t1].[value]

4、单表的查询

 

var query = from tc in db.tbClass

              where tc.ClassID == "1"

            //查询表tbClass

             select new {

             ClassID=tc.ClassID,

             ClassName=tc.ClassName

 

5、多表内连接查询

var query = from s in db.tbStudents

                            join c in db.tbClass on s.ClassID equals c.ClassID

                            where s.ClassID == 3

                            select new

                            {

                                ClassID = s.ClassID,

                                ClassName = c.ClassName,

                                Student = new

                                {

                                    Name = s.Name,

                                    StudentID = s.StudentID

                                }

                            };

6、外连接

var query = from s in db.tbStudents

                            join c in db.tbClass on s.ClassID equals c.ClassID into tbC

                            from tbCw in tbC.DefaultIfEmpty()

                            where s.ClassID == 3

                            select new

                            {

                                ClassID = s.ClassID,

                                ClassName = tbCw.ClassName,

                                Student = new

                                {

                                    Name = s.Name,

                                    StudentID = s.StudentID

                                }

                            };

7、多表关联

listUser = (from u in dbcontext.t_user

                        join g in dbcontext.t_grade on u.gradeID equals g.gradeID

                        join s in dbcontext.t_states on u.state equals s.statesID

                        join c in dbcontext.t_character on u.levelID equals c.levelID

                        select new userModel()

                        {

                          userID=u.userID,

                          userName=u.userName,

                          userGrade=g.userGrade,

                          userStates=s.userSates,

                          userLevel=c.userLevel,

                          totalMcoin=u.totalMcoin

                        }).ToList();