将 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 获取大块结果时效率低下，那么您最好的选择是 Dapper.为自己获取一份 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 现在具有等同于 skip 和 take 的子句.如果您直接编写 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 的事实获取所有外部行.然后我使用 ToArray() 实现了这两个部分，并使用 LINQ 将它们连接到内存中.这使执行时间从 10 分钟缩短到 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屋！