如何将Linq查询序列化到数据库

Question

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

我很难理解该怎么做。

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

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

有人能给我看一个linq语句被序列化，然后反序列化并运行的简单例子吗？

Answer 1

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

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

然后，您可以在实体框架原始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查询，当您准备好运行它们时，您可以使用实体框架原始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");