最近工作中遇到数据库组合查询带来的一些问题，因此有必要调研一下Linq to Objects Join Linq to Entity。参考一些网友的代码案例，深入实践了一下使用EntityFramework Code First 下的组合查询。
准备环节：
（一） 在VS下创建一个控制台应用程序（LinqToObjectJoinEntity），定义一个MyObject类，如下：
public class MyObject
{
public int Identity { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
（二）然后再定义一个Entity类及EntityContext类。
public class Entity
{
public int EntityId { get; set; }
public string Name { get; set; }
public string Notes { get; set; }
}
public class EntityContext : DbContext
{
public IDbSet<Entity> Entitys { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention>(); // 此处是避免使用EF创建出来的Entity表为复数形式，顺便吐槽下微软默认给复数的设计
modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.OneToManyCascadeDeleteConvention>(); // 此处是避免使用EF创建出的数据库包含数据迁移表
base.OnModelCreating(modelBuilder);
}
}
（三）使用EF创建准备数据（在Main（）函数中实现）：
using (var db = new EntityContext())
{
db.Entitys.Add(new Entity { EntityId = 1, Name = "Entity", Notes = "Notes" });
db.Entitys.Add(new Entity { EntityId = 2, Name = "Frame", Notes = "Mates" });
db.Entitys.Add(new Entity { EntityId = 3, Name = "Work", Notes = "Honor" });
db.SaveChanges();
}
Coding实践：
（1）重现Linq to Object Join Linq to Entity
#region Reproduce Linq to Object Join Linq to Entity
var objectNames = (from myObject in myObjects
join entity in db.Entitys
on myObject.Identity equals entity.EntityId
select myObject.Name).ToList();
#endregion
使用Sql Profiler观察到的查询语句如下：
SELECT
[Extent1].[EntityId] AS [EntityId],
[Extent1].[Name] AS [Name],
[Extent1].[Notes] AS [Notes]
FROM [dbo].[Entity] AS [Extent1]
属于全表查询，此乃Linq to objects Join Linq to Entity一大弊害。
（2）重现 Linq to Entity Join Linq to Object
#region Reproduce Linq to Entity Join Linq to Object
var entityName = (from entity in db.Entitys
join myObject in myObjects
on entity.EntityId equals myObject.Identity
select entity.Name).ToList();
#endregion
这个地方运行时会抛异常：
Only Primitive types ('Such as Int32, string, and Guid') are supported in this context
中文意思是“无法创建类型为“项目名.MyObject”的常量值。此上下文仅支持基元类型(“例如 Int32、String 和 Guid”)"
看来在涉及这种操作时，我们内存中的数据还不能是非基元类型。List<MyObject> objectList = new List<MyObject>();
MyObject要为int32, string或者Guid，才能运行通过，并且不是整表查询，而是针对name列的单独查询。
（3）改进 Linq to Entity Join Linq to Object
#region Linq to Entity Join Linq to Object(Resolve)
var identities = myObjects.Select(o => o.Identity);
var entitytNames = (from entity in db.Entitys
join identity in identities
on entity.EntityId equals identity
select entity.Name).ToList();
#endregion
这里Sql Profile监测到的查询语句为：
SELECT
[Extent1].[Name] AS [Name]
FROM [dbo].[Entity] AS [Extent1]
INNER JOIN (SELECT
1 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable1]
UNION ALL
SELECT
2 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable2]
UNION ALL
SELECT
3 AS [C1]
FROM ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2] ON [Extent1].[EntityId] = [UnionAll2].[C1]
虽是麻烦了些，查出来的东西只有一个。
此文只是针对性的简述下Linq to Object Join Linq to Entity的场景，在日常工作中可能不止于此。
代码见：https://github.com/Jashinck/LinqToObjectJoinEntity
墨匠
