将Linq迁移到SQL代码到.Net Core [英] Migrating Linq to SQL code to .Net Core

问题描述

我们有一些旧代码，这些代码使用Linq to SQL作为ORM.我们希望将此逻辑迁移到.Net Core，以便将其容纳在linux服务器上.据我所知，L2S不包含在.Net Core中.

We have some legacy code that uses Linq to SQL as the ORM. We'd like to migrate this logic to .Net Core so that we can house it on a linux server. As far as I can tell, L2S is not included in .Net Core.

阻力最小的迁移路径是什么?

What is the migration path of least resistance?

推荐答案

如果您使用L2S是因为EF在使用Skip and Take来获取大块结果时效率低下，那么最好的选择是 LINQPad 的副本，并使用它来获取每个LINQ表达式的生成的SQL.

If you used L2S because EF is inefficient at using Skip and Take for fetching large results as chunks, then your best bet is Dapper. Get yourself a copy of LINQPad and use it to get the generated SQL for each of your LINQ expressions.

L2S在实际查询周围包装了一些奇怪的SQL，以使用SQL的rownumber函数实现跳过和采用.如果您使用的是最新版本的SQL Server，则不需要这样做，因为TSQL现在具有等效于跳过和采用的子句.如果您直接编写SQL并生成可理解的SQL，但不会引起追随者的WTF，但是LINQ方式可在所有版本的SQL Server上使用，这非常方便.

L2S wraps some bizarre SQL around the actual query to use SQL's rownumber function to implement skip and take. If you are using the latest version of SQL Server then you don't need this because TSQL now has clauses equivalent to skip and take. This is handy if you are writing SQL directly and produces comprehensible SQL that won't induce WTF in those who follow, but the LINQ way works on all versions of SQL Server.

然后将此SQL与Dapper一起使用，它将为您完成ORM部分.它还对类似于L2S的类型映射参数提供了适当的支持，因此您可以避免构建SQL字符串和注入漏洞.

Then use this SQL with Dapper, which will do the ORM part for you. It also has proper support for type mapping parameters similar to L2S so you can avoid building SQL strings and injection vulnerabilities.

如果您希望所有聪明的人用集合成员身份隐含的FK值来构造对象图，那么您就不走运了，您必须手动对其进行编码.

If you want all the smarts for constructing object graphs with FK values implied by collection membership then you're out of luck, you'll have to code it by hand.

EF过去没有那么恐怖.EF Core比EF更简单，同时保留了许多优点.我目前在工作中的项目上使用EF Core，这不是EF曾经的灾难.

EF is less horrible than it used to be. EF Core is simpler than EF while retaining many of the benefits. I am currently using EF Core on a project at work and it's not the disaster EF once was.

我确实需要帮助进行外部联接.留给自己的设备，LINQ获取内部部分，然后针对每个内部行对其外部部分进行单独的查询.

I did have to help with an outer join. Left to its own devices, LINQ fetched the inner part and then for each inner row ran a separate query for its outer portion.

我通过显式固定了这一点，以获取内部部分并将键集构造为int数组.另一个LINQ语句利用 Array.Contains 映射到使用索引的IN的事实，提取​​了所有外部行的 all .然后，我使用 ToArray()实现了这两个部分，并使用LINQ将它们连接到内存中.这使执行时间从十分钟缩短到300毫秒.

I fixed this by explicitly fetching the inner part and constructing a keyset as an array of int. Another LINQ statement fetched all of the outer rows exploiting the fact that Array.Contains maps to IN which uses indexes. Then I materialised both parts using ToArray() and used LINQ to join them in memory. This brought execution time down from ten minutes to 300ms.

您不必这样做；L2S不会首先提出来.但是至少有一个简单的通用解决方案.

You shouldn't have to do this; L2S wouldn't have cocked it up in the first place. But at least there's a straightforward general solution.

我的解决方案的一个缺点是它不能很好地适应渐进式提取.

A shortcoming of my solution is that it is not well adapted to progressive fetch.

Dapper可以返回动态结果.我发现这是SQL与C#之间的良好桥梁.列及其名称受SQL控制.由于C#区分大小写，而SQL不区分大小写，所以这可能有点脆弱，但是使用SQL编写代码后，您至少可以看到它是什么并对其进行修复.

Dapper can return dynamic results. I find this is a good bridge between SQL and C#. The columns and their names are governed by the SQL. This can be a little fragile since C# is case sensitive and SQL isn't, but with the SQL in your code you can at least see what it is and fix it.

更重要的是，您可以直接在这些动态结果上使用LINQ.

More to the point, you can use LINQ directly on these dynamic results.

这篇关于将Linq迁移到SQL代码到.Net Core的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！