c# - 如何将 Linq 查询序列化到数据库？

Question

我需要能够将 Linq 查询作为字符串存储在数据库中，然后加载它并在以后运行它。

我很难想清楚如何做到这一点。

我发现这个库说它可以做到:https://github.com/esskar/Serialize.Linq

但保存文档的博客已关闭，我无法从示例中弄清楚，发生了太多事情。

任何人都可以向我展示或将我链接到一个简单的 linq 语句示例，该示例先被序列化，然后反序列化并运行吗？

Answer 1

我不知道这是否是性能方面的最佳选择(我不确定它是好是坏)，但这是实现您想要的功能的一个选择。

IQueryable ToString() 方法允许您以编程方式检索 IQueryable 对象将生成并作为数据库查询运行的 SQL。

然后您可以在 Entity Framework 原始 SQL 查询中使用该 SQL 字符串。

下面的示例将使用开箱即用的 Microsoft ASP.NET Identity DbContext。

IQueryable ToString() 示例:

ApplicationDbContext db = new ApplicationDbContext();
IQueryable query = db.Users.Where(w => w.Email != null);
string generatedSql = query.ToString();

字符串 generatedSql 将包含以下内容:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Email] AS [Email], 
[Extent1].[EmailConfirmed] AS [EmailConfirmed], 
[Extent1].[PasswordHash] AS [PasswordHash], 
[Extent1].[SecurityStamp] AS [SecurityStamp], 
[Extent1].[PhoneNumber] AS [PhoneNumber], 
[Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
[Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
[Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
[Extent1].[LockoutEnabled] AS [LockoutEnabled], 
[Extent1].[AccessFailedCount] AS [AccessFailedCount], 
[Extent1].[UserName] AS [UserName]
FROM [dbo].[AspNetUsers] AS [Extent1]
WHERE [Extent1].[Email] IS NOT NULL

这是一个在 IQueryable 对象上使用 Include() 方法的示例，以显示这将生成哪种 SQL:

ApplicationDbContext db = new ApplicationDbContext();

IQueryable query = db.Users
    .Include(i => i.Claims)
    .Include(i => i.Roles)
    .Where(w => w.Email != null);

string generatedSql = query.ToString();

添加 Include 方法后，字符串 generatedSql 将包含以下内容:

SELECT 
[UnionAll1].[AccessFailedCount] AS [C1], 
[UnionAll1].[Id] AS [C2], 
[UnionAll1].[Email] AS [C3], 
[UnionAll1].[EmailConfirmed] AS [C4], 
[UnionAll1].[PasswordHash] AS [C5], 
[UnionAll1].[SecurityStamp] AS [C6], 
[UnionAll1].[PhoneNumber] AS [C7], 
[UnionAll1].[PhoneNumberConfirmed] AS [C8], 
[UnionAll1].[TwoFactorEnabled] AS [C9], 
[UnionAll1].[LockoutEndDateUtc] AS [C10], 
[UnionAll1].[LockoutEnabled] AS [C11], 
[UnionAll1].[AccessFailedCount1] AS [C12], 
[UnionAll1].[UserName] AS [C13], 
[UnionAll1].[C1] AS [C14], 
[UnionAll1].[Id1] AS [C15], 
[UnionAll1].[UserId] AS [C16], 
[UnionAll1].[ClaimType] AS [C17], 
[UnionAll1].[ClaimValue] AS [C18], 
[UnionAll1].[C2] AS [C19], 
[UnionAll1].[C3] AS [C20], 
[UnionAll1].[C4] AS [C21]
FROM  (SELECT 
    CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
    [Extent1].[AccessFailedCount] AS [AccessFailedCount], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[EmailConfirmed] AS [EmailConfirmed], 
    [Extent1].[PasswordHash] AS [PasswordHash], 
    [Extent1].[SecurityStamp] AS [SecurityStamp], 
    [Extent1].[PhoneNumber] AS [PhoneNumber], 
    [Extent1].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
    [Extent1].[TwoFactorEnabled] AS [TwoFactorEnabled], 
    [Extent1].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
    [Extent1].[LockoutEnabled] AS [LockoutEnabled], 
    [Extent1].[AccessFailedCount] AS [AccessFailedCount1], 
    [Extent1].[UserName] AS [UserName], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[UserId] AS [UserId], 
    [Extent2].[ClaimType] AS [ClaimType], 
    [Extent2].[ClaimValue] AS [ClaimValue], 
    CAST(NULL AS varchar(1)) AS [C2], 
    CAST(NULL AS varchar(1)) AS [C3], 
    CAST(NULL AS varchar(1)) AS [C4]
    FROM  [dbo].[AspNetUsers] AS [Extent1]
    LEFT OUTER JOIN [dbo].[AspNetUserClaims] AS [Extent2] ON [Extent1].[Id] = [Extent2].[UserId]
    WHERE [Extent1].[Email] IS NOT NULL
UNION ALL
    SELECT 
    2 AS [C1], 
    [Extent3].[AccessFailedCount] AS [AccessFailedCount], 
    [Extent3].[Id] AS [Id], 
    [Extent3].[Email] AS [Email], 
    [Extent3].[EmailConfirmed] AS [EmailConfirmed], 
    [Extent3].[PasswordHash] AS [PasswordHash], 
    [Extent3].[SecurityStamp] AS [SecurityStamp], 
    [Extent3].[PhoneNumber] AS [PhoneNumber], 
    [Extent3].[PhoneNumberConfirmed] AS [PhoneNumberConfirmed], 
    [Extent3].[TwoFactorEnabled] AS [TwoFactorEnabled], 
    [Extent3].[LockoutEndDateUtc] AS [LockoutEndDateUtc], 
    [Extent3].[LockoutEnabled] AS [LockoutEnabled], 
    [Extent3].[AccessFailedCount] AS [AccessFailedCount1], 
    [Extent3].[UserName] AS [UserName], 
    CAST(NULL AS int) AS [C2], 
    CAST(NULL AS varchar(1)) AS [C3], 
    CAST(NULL AS varchar(1)) AS [C4], 
    CAST(NULL AS varchar(1)) AS [C5], 
    [Extent4].[UserId] AS [UserId], 
    [Extent4].[RoleId] AS [RoleId], 
    [Extent4].[UserId] AS [UserId1]
    FROM  [dbo].[AspNetUsers] AS [Extent3]
    INNER JOIN [dbo].[AspNetUserRoles] AS [Extent4] ON [Extent3].[Id] = [Extent4].[UserId]
    WHERE [Extent3].[Email] IS NOT NULL) AS [UnionAll1]
ORDER BY [UnionAll1].[Id] ASC, [UnionAll1].[C1] ASC

---

然后您可以根据需要存储这些 SQL 查询，当您准备好运行它们时，您可以使用 Entity Framework 原始 SQL 查询。

像这样:

ApplicationDbContext db = new ApplicationDbContext();
var users = db.Users.SqlQuery("Your SQL here");


//or this if you need your DbSet to be dynamic
ApplicationDbContext db = new ApplicationDbContext();
var users = db.Set<User>.SqlQuery("Your SQL here");


//or this for commands
ApplicationDbContext db = new ApplicationDbContext();
db.Database.ExecuteSqlCommand("Your SQL command string here");