英文:
Lambda Expression Sql to C# DateTime
问题
我有以下的SQL数据库查询：
SELECT a.GroupCode, a.GroupDate
FROM TableP
WHERE YEAR(a.GroupDate) = '2023'
AND a.GroupCode LIKE '0100%';
我该如何将它转换为Lambda表达式？我所做的如下：
List<TableP> list = await _context.TablePs
.Where(x => x.GroupCode.StartsWith(request.GroupCode) && x.GroupDate == request.GroupDate)
.ToListAsync(cancellationToken);
所以，从上面的Lambda表达式中，我想将 x.GroupDate 更改为 YEAR(a.GroupDate)。然后，我将创建一个string类型的参数。
请给予建议。谢谢。
英文:
I have the following query in sql database:<br>
SELECT a.GroupCode, a.GroupDate
FROM TableP
WHERE YEAR(a.GroupDate) = '2023'
AND a.GroupCode LIKE '0100%';
How can I change it into Lambda Expression? What I did is like below:<br>
List<TableP> list = await _context.TablePs
.Where(x => x.GroupCode.StartsWith(request.GroupCode) && x.GroupDate == request.GroupDate)
.ToListAsync(cancellationToken);
So from the lambda above, I want to change x.GroupDate like YEAR(a.GroupDate). Then I will create a parameter as a string.
Advice please.<br>
Thank you.
答案1
得分: 3
如果 DataType GroupDate 是 DateTime，则可以使用 g.GroupDate.Year 和 linq 翻译成 DATEPART(year, .[GroupDate])
示例
```csharp
var _result = context.TablePs
.Where(d => x.GroupCode.StartsWith(request.GroupCode)
&& d.GroupDate.Year == request.GroupDate.Year).ToList();
我用性能分析器获取了我的查询
SELECT *
FROM [Persons] AS
WHERE (.[GroupCode] LIKE N'dd%') AND DATEPART(year,
.[GroupCode]) = 2023
<details>
<summary>英文:</summary>
if DataType GroupDate is DateTime ,You can use g.GroupDate.Year and linq translate to DATEPART(year, .[GroupDate])
Example
var _result = context.TablePs
.Where(d => x.GroupCode.StartsWith(request.GroupCode)
&& d.GroupDate.Year == request.GroupDate.Year).ToList();
i qet my query with profiler
SELECT *
FROM [Persons] AS
WHERE (
.[GroupCode] LIKE N'dd%') AND DATEPART(year,
.[GroupCode]) = 2023
</details>
# 答案2
**得分**: 2
请注意，这会使您的SQL日期条件不可搜索（意味着如果您有任何索引，数据库将无法使用GroupDate列上的任何索引） - 因此，通常更好的解决方案是使用范围 - 所以在SQL中，而不是`WHERE YEAR(a.GroupDate) = 2023`，您会使用类似以下的内容：`WHERE a.GroupDate >= '01012023' AND a.GroupDate < '01012024'`。
尽管这*可能*会对性能产生影响（假设您有一个与查询相关的索引，并且表足够大以从该索引中受益），但它确实会使代码编写更加繁琐，特别是如果您正在编写linq而不是直接SQL - 所以除非遇到性能问题，否则不要急于这样编写它。
一个应该能够转换成类似的linq表达式可能是这样的（在此处直接编写，尚未进行测试）：
```csharp
Where(x => x.GroupCode.StartsWith(request.GroupCode)
&& x.GroupDate >= new DateTime(request.GroupDate.Year, 1, 1)
&& x.GroupDate < new DateTime(request.GroupDate.Year + 1, 1, 1))
英文:
I'm not sure it will go through translation to SQL, but you can try this:
Where(x => x.GroupCode.StartsWith(request.GroupCode)
&& x.GroupDate.Year == request.GroupDate.Year)
Please note, however, that this makes your SQL date condition not sargable (meaning your database can't use any indexes on the GroupDate column if you have any) - so a better solution usually would be to use a range - so in SQL instead of
WHERE YEAR(a.GroupDate) = 2023 you'll use something like
WHERE a.GroupDate >= '01012023' AND a.GroupDate < '01012024'.
While this might have an effect on performance (assuming you have an index that is relevant to the query and the table is big enough to benefit from using that index) - it does make a much more cumbersome code writing, especially if you're writing linq and not directly SQL - so I wouldn't rush to write it like that unless you encounter a performance problem.
A linq expression that should translate to something like that might be something along these lines (written directly here without testing first)
Where(x => x.GroupCode.StartsWith(request.GroupCode)
&& x.GroupDate >= new DateTime(request.GroupDate.Year, 1, 1)
&& x.GroupDate < new DateTime(request.GroupDate.Year + 1, 1, 1))
答案3
得分: -1
你可以在你的数据库中创建一个“view”，在这个“view”中，你可以在“select”子句中包含YEAR(GroupDate) as YGroupDate的计算作为一个字段。将这个视图链接到你的ORM并为其编写lambda函数，可以让你通过YGroupDate来搜索你的记录。
英文:
You can create a view in your database, where you can include the YEAR(GroupDate) as YGroupDate computation as a field in the select clause. Linking that view to your ORM and writing lambda for it would allow you to search for your your records via YGroupDate.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论