将SQL转换为linq以引入C＃ [英] Translate SQL to linq to entites C#

问题描述

你好我需要将以下sql翻译成linq以使用EF 6来引用c＃iam

hello i need to translate the following sql to linq to entites c# iam using EF 6

SELECT  ProductID, BillType, SUM(PermitQuantity) AS quantity,  BillDate  = DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0)
 FROM  ViewRpt_ProductsTrans
 WHERE BillType IN (2,3)
 AND  ProductCode='79'
 AND BillIsPermitted= 1
 GROUP BY  DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0),ProductID,BillType
 ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0)

我无法翻译以下部分

iam unable to translate the following part

DATEADD(MONTH, DATEDIFF(MONTH, 0, BillDate), 0)

我尝试过：

What I have tried:

var trans = (from p in _context.ViewRpt_ProductsTrans
                        let groupDate = new DateTime(p.BillDate.Value.Year, p.BillDate.Value.Month, 0, 0, 0, 0)
                        where
                                p.ProductID == productId &&
                                p.BillIsPermitted == true &&
                                (p.BillType == 3 || p.BillType == 2)
                        group p by
                        new
                        {
                            p.ProductID,
                            p.BranchID,
                            p.BillType,
                            groupDate
                        } into g

                        select new BranchesProductTransViewModel()
                        {
                            BillDate = g.Key.groupDate,
                            BillType = g.Key.BillType.Value,
                            BranchId = g.Key.BranchID.Value,
                            ProductId = g.Key.ProductID,
                            Quantity = g.Sum(x => x.PermitQuantity).GetValueOrDefault(0)
                        });

推荐答案

这样的事情应该有效：

Something like this should work:

let groupDate = DbFunctions.AddMonths(new DateTime(1753, 1, 1), EntityFunctions.DiffMonths(new DateTime(1753, 1, 1), p.BillDate))

（使用命名空间 System.Data.Entity ） < br $>


或：

(Using the namespace System.Data.Entity)

Or:

let groupDate = SqlFunctions.DateAdd("month", SqlFunctions.DateDiff("month", new DateTime(1753, 1, 1), p.BillDate), new DateTime(1753, 1, 1))

（使用命名空间 System.Data.Entity.SqlServer ）



两者之间的主要区别在于 SqlFunctions 仅适用于SQL Server，而 EntityFunctions 也适用于其他提供商。



注意： 1753年1月1日是SQL支持的最早日期 datetime 类型。如果您使用的是SQL 2008或更高版本，则应将列定义为 datetime2 ，在这种情况下，您可以使用 DateTime.MinValue 而不是。

(Using the namespace System.Data.Entity.SqlServer)

The main difference between the two is that SqlFunctions only works for SQL Server, whereas EntityFunctions should work for other providers as well.

NB: 1st January 1753 is the earliest date supported by SQL's datetime type. If you're using SQL 2008 or later, your columns should be defined as datetime2, in which case you could use DateTime.MinValue instead.

这篇关于将SQL转换为linq以引入C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！