高性能ORM框架XLinq功能详细介绍

之前简单介绍了XLinq的一些功能，有很多功能都没有提到，现在给XLinq加了一些功能，这次把所有功能都介绍一遍。

设计目标

易用性

在使用一个框架的时候

应该没几个人会喜欢写一大堆的配置文件吧

也应该没几个人会喜欢为了完成一个小功能却需要写一大堆代码

这是XLinq开发的首要目标之一，就是尽可能提高易用性

最小配置的情况下仅需要一句连接字符串的配置就可以使用

默认支持的是Sql Server 2008 R2数据库，理论上说也大部分支持了sql server系的其他数据库
高性能

目前针对查询时的DataReader转List和批量插入有针对性优化，其他的不太重要的例如批量更新这个并没有太多优化
易于调试

出了问题能够以最快速度让使用者定位到问题，其实这些是细节问题
支持LINQ

这个其实是跟易用性挂勾的，园子里面有大神写的ORM，声称"无Linq"，其实我也不太想用Linq，因为解析Linq实在太难，坑太多，但又找不到另一种比Linq更让我满意的方案。然后我去看了他的ORM的使用方法，发现一旦复杂起来不见得会比Linq更清晰，并且实体类的设计实在是···

Linq实现复杂的语法确实比较蛋疼，sql语句里面直接来几个case when，linq就得写晕过去，至少解析的时候会晕过去。

但在我看来，既然linq语句都比较复杂了，那肯定是有很多的逻辑在里面，就像上面说的case when，那么为什么不把这些逻辑分拆成好几个linq去执行呢？或者干脆点写存储过程里面。
多数据库支持

使用方法

建立控制台应用程序，暂时只支持.net 4.5.2版本
安装xlinq nuget包
建立数据库XLinq及相关表
添加配置文件
1. <connectionStrings>
2. <add name="test" connectionString="Data Source=(local);;initial catalog=XLinq;User Id=xinchen;Password=123456"/>
3. </connectionStrings>
建立TestDataContext类和User实体类
1. public class TestDataContext:DataContext
2. {
3. public TestDataContext():base("test")
4. {
6. }
7. }
8. public class User
9. {
10. public int Id { get; set; }
11. public string Username { get; set; }
12. }
开始使用
1. TestDataContext db = new TestDataContext();
2. db.Set<User>().ToList();

查询

单表查询

Lambda版：
1. db.Set<User>().Where(x => x.Username == "xxxx").ToList();
LINQ版：
1. (from user in db.Set<User>() where user.Username == "xxxx" select user).ToList();
LINQ版看起来明显感觉比较麻烦，所以在简单查询的时候我更倾向于Lambda表达式

上面的语句生成的代码是一致的
1. SELECT
2. [Users1].[Id] [Id]
3. ,[Users1].[Username] [Username]
4. FROM [Users] [Users1] WHERE ([Users1].[Username] = @param2)
多表查询

建立实体类的过程不再说

两个表连接查询，因为用Lambda实现比较复杂，所以后面都不再用Lambda实现
1. var query = from user in db.Set<User>()
2. join userRole in db.Set<UserRole>() on user.Id equals userRole.UserId
3. where user.Id == 1 && userRole.RoleId == 1
4. select new
5. {
6. user.Id,
7. userRole.RoleId,
8. user.Username
9. };
生成的语句
1. SELECT
2. [user].[Id] [Id]
3. ,[userRole].[RoleId] [RoleId]
4. ,[user].[Username] [Username]
5. FROM
6. [Users] [user] INNER JOIN [UserRoles] [userRole]
7. ON [user].[Id] = [userRole].[UserId]
9. WHERE (([user].[Id] = @param1) AND ([userRole].[RoleId] = @param2))
五个表连接查询
1. var query = from user in db.Set<User>()
2. join userRole in db.Set<UserRole>() on user.Id equals userRole.UserId
3. join rolePrivilege in db.Set<RolePrivilege>() on userRole.RoleId equals rolePrivilege.RoleId
4. join priviege in db.Set<Privilege>() on rolePrivilege.PrivilegeId equals priviege.Id
5. join role in db.Set<Role>() on userRole.RoleId equals role.Id
6. where user.Id == 1 && userRole.RoleId == 1
7. select new
8. {
9. user.Id,
10. userRole.RoleId,
11. user.Username,
12. PrivilegeName = priviege.Name,
13. RoleName = role.Name
14. };
生成的语句
1. SELECT
2. [user].[Id] [Id]
3. ,[userRole].[RoleId] [RoleId]
4. ,[user].[Username] [Username]
5. ,[priviege].[Name] [PrivilegeName]
6. ,[role].[Name] [RoleName]
7. FROM
8. [Users] [user] INNER JOIN [UserRoles] [userRole]
9. ON [user].[Id] = [userRole].[UserId]
11. INNER JOIN [RolePrivileges] [rolePrivilege]
12. ON [userRole].[RoleId] = [rolePrivilege].[RoleId]
14. INNER JOIN [Privileges] [priviege]
15. ON [rolePrivilege].[PrivilegeId] = [priviege].[Id]
17. INNER JOIN [Roles] [role]
18. ON [userRole].[RoleId] = [role].[Id]
20. WHERE (([user].[Id] = @param5) AND ([userRole].[RoleId] = @param6))
左连接查询

因为linq本身的左连接写法比较蛋疼，所以xlinq也没有办法，后面会想办法简化
1. var query = from user in db.Set<User>()
2. join userRole in db.Set<UserRole>() on user.Id equals userRole.UserId into urs
3. from ur in urs.DefaultIfEmpty()
4. where user.Id == 1 && ur.RoleId == 1
5. select new
6. {
7. user.Id,
8. ur.RoleId,
9. user.Username
10. };
生成的语句
1. SELECT
2. [user].[Id] [Id]
3. ,[userRole].[RoleId] [RoleId]
4. ,[user].[Username] [Username]
5. FROM
6. [Users] [user] Left JOIN [UserRoles] [userRole]
7. ON [user].[Id] = [userRole].[UserId]
9. WHERE (([user].[Id] = @param5) AND ([userRole].[RoleId] = @param6))
延迟加载
1. var query = db.Set<User>().Where(x => x.Username == "xxxxx").Select(x => x.Id);
2. foreach (var item in query)
3. {
5. }
自连接查询
1. var query = from user1 in db.Set<User>()
2. join user2 in db.Set<User>() on user1.Id equals user2.Id
3. select user1;
生成的语句
1. SELECT
2. [user1].[Id] [Id]
3. ,[user1].[Username] [Username]
4. FROM
5. [Users] [user1] INNER JOIN [Users] [user2]
6. ON [user1].[Id] = [user2].[Id]
SQL语句查询
1. db.SqlQuery<User>("select * from dbo.users", new Dictionary<string, object>());
分页查询
1. var page = 1;
2. var pageSize = 10;
3. var query = (from user in db.Set<User>()
4. join userRole in db.Set<UserRole>() on user.Id equals userRole.UserId
5. join rolePrivilege in db.Set<RolePrivilege>() on userRole.RoleId equals rolePrivilege.RoleId
6. join priviege in db.Set<Privilege>() on rolePrivilege.PrivilegeId equals priviege.Id
7. join role in db.Set<Role>() on userRole.RoleId equals role.Id
8. where user.Id == 1 && userRole.RoleId == 1
9. orderby user.Id descending
10. select new
11. {
12. user.Id,
13. userRole.RoleId,
14. user.Username,
15. PrivilegeName = priviege.Name,
16. RoleName = role.Name
17. }).Skip((page - 1) * pageSize).Take(pageSize);
生成的语句
1. SELECT [_indexTable].[Id],[_indexTable].[RoleId],[_indexTable].[Username],[_indexTable].[PrivilegeName],[_indexTable].[RoleName] FROM (SELECT [user].[Id] [Id]
2. ,[userRole].[RoleId] [RoleId]
3. ,[user].[Username] [Username]
4. ,[priviege].[Name] [PrivilegeName]
5. ,[role].[Name] [RoleName]
6. ,ROW_NUMBER() OVER(ORDER BY [user].[Id]) #index
7. FROM [Users] [user] INNER JOIN [UserRoles] [userRole]
8. ON [user].[Id] = [userRole].[UserId]
10. INNER JOIN [RolePrivileges] [rolePrivilege]
11. ON [userRole].[RoleId] = [rolePrivilege].[RoleId]
13. INNER JOIN [Privileges] [priviege]
14. ON [rolePrivilege].[PrivilegeId] = [priviege].[Id]
16. INNER JOIN [Roles] [role]
17. ON [userRole].[RoleId] = [role].[Id]
19. WHERE (([user].[Id] = @param5) AND ([userRole].[RoleId] = @param6)) ) _indexTable where [_indexTable].[#index] BETWEEN 0 AND 10
动态查询
1. IQueryable<User> query = db.Set<User>();
2. var filters = new List<SqlFilter>();
3. filters.Add(SqlFilter.Create("Id", Operation.Equal, 1));
4. filters.Add(SqlFilter.Create("Username", Operation.Like, "aaa"));
5. query = query.Where(filters);
生成的语句
1. SELECT
2. [Users1].[Id] [Id]
3. ,[Users1].[Username] [Username]
4. FROM [Users] [Users1] WHERE (([Users1].[Id] = @param2) AND CHARINDEX(@param3,[Users1].[Username])>0)
取日期查询

这个功能主要针对EF中无法直接取日期的问题
1. var query = db.Set<User>().Where(x => x.LoginTime.Date == DateTime.Now.Date);
2. SELECT
3. [Users1].[Id] [Id]
4. ,[Users1].[Username] [Username]
5. ,[Users1].[LoginTime] [LoginTime]
6. FROM [Users] [Users1] WHERE (CONVERT(DATE,[Users1].[LoginTime],211) = @param3)
计算天数查询
1. var query = db.Set<User>().Where(x => (x.LoginTime - DateTime.Now).TotalDays <= 7);
生成的语句
1. SELECT
2. [Users1].[Id] [Id]
3. ,[Users1].[Username] [Username]
4. ,[Users1].[LoginTime] [LoginTime]
5. FROM [Users] [Users1] WHERE (DATEDIFF(DAY,[Users1].[LoginTime],@param2) <= @param3)

修改

先查询后修改（注意只有十条数据以内才会支持修改）
1. var query = db.Set<User>().FirstOrDefault();
2. query.Username = "xxxxxxx";
3. db.SaveChanges();
直接修改
1. db.Set<User>().Where(x => x.Id == 1).Update(x => new User
2. {
3. Username = "xxxxxxxxxxxxx"
4. });
Update子句必须采用这写法才会有效

删除

先查询后删除
1. var query = db.Set<User>().FirstOrDefault();
2. db.Set<User>().Remove(query);
3. db.SaveChanges();
直接删除
1. db.Set<User>().Where(x => x.Id == 1).Delete();

事务

普通事务
1. using (var scope = new TransactionScope())
2. {
3. db.Set<User>().Where(x => x.Id == 1).Delete();
4. scope.Complete();
5. }
嵌套事务
1. using (var scope = new TransactionScope())
2. {
3. db.Set<User>().Where(x => x.Id == 1).Delete();
4. using (var s1 = new TransactionScope())
5. {
6. db.Set<Privilege>().Where(x => x.Id == 1).Delete();
7. s1.Complete();
8. }
9. scope.Complete();
10. }

调试支持

在调试的时候可直接看到SQL语句

多数据库支持

通过配置文件实现多数据库支持

<configSections>
<section name="xlinq" type="Xinchen.XLinq.ConfigSection,Xinchen.XLinq"/>
</configSections>
<xlinq connectionStringName="test" dataBase="SQLite"></xlinq>

链式Api

有没有园友注意到，我上面用的User实体并没有进行任何特殊处理，但实际翻译出来的语句是识别的Users表

这其实是"抄"的EF的约定大于配置的原则，默认情况下，实体名的复数就是表名，实体中的Id识别为主键并自动增长

如果需要自定义这些规则，则需要使用特性或链式API，我现在更喜欢链式API

public class TestDataContext:DataContext
{
public TestDataContext():base("test")
{
}
protected override void ConfigurationModel(Xinchen.XLinq.Configuation.EntityConfigurationManager entityConfiguration)
{
entityConfiguration.Entity<User>().TableName("Users").Key(x => x.Id, Xinchen.DbUtils.DataAnnotations.DataSourceTypes.AutoIncreament);
}
}

重写DataContext的ConfigurationModel方法即可使用链式API对实体相关信息进行配置，上面是指定了User实体的表名、主键及主键的数据来源为自动增长

创建表

若要创建表，则几乎必须使用链式API对实体进行配置之后才能进行自动创建

另外这个功能有一定的限制，默认情况下不会启用该功能，若启用了也只有在数据库中一个表都没有的情况下才会自动创建表

这样做是因为真正的数据库运行环境其实很可能压根不允许自动创建表，然后就是下面这个样子，这样一来这个功能其实并没有太大用，只有在第一次初始化数据库的时候才会用。

当然也可以始终自动创建表，这个需要稍微配置一下，当allwayAutoCreateTables为true时并且autoCreateTables为true时，将始终自动创建表

下面演示创建一个Test表

DataContext中的配置

public class TestDataContext : DataContext
{
public TestDataContext()
: base("test")
{
}
protected override void ConfigurationModel(Xinchen.XLinq.Configuation.EntityConfigurationManager entityConfiguration)
{
var testEntity = entityConfiguration.Entity<Test>().TableName("TestTables").Key(x => x.Id, Xinchen.DbUtils.DataAnnotations.DataSourceTypes.AutoIncreament);
testEntity.Property(x => x.Id).Name("TId");
testEntity.Property(x => x.Name).MaxLength(100);
}
}

创建的表

自定义Provider（有这功能，但没有测试）

性能

查询
1. class Program
2. {
3. static int count = 1;
4. static int rowCount = 1000000;
5. static void Main(string[] args)
6. {
7. TestDataContext xlinqDb = new TestDataContext();
8. TestDbContext efDb = GetEF(null);
9. var userDB = xlinqDb.Set<User>();
10. var userRoleDb = xlinqDb.Set<UserRole>();
11. var efUserDb = efDb.Users;
12. var efUserRoleDb = efDb.UserRoles;
13. ExecuteTimer("XLinq使用Linq", () =>
14. {
15. for (int i = 0; i < count; i++)
16. {
17. userDB.Take(rowCount).ToList();
18. }
19. });
20. ExecuteTimer("XLinq使用SQL", () =>
21. {
22. var sql = "select top " + rowCount + " * from dbo.users";
23. var dict = new Dictionary<string, object>();
24. for (int i = 0; i < count; i++)
25. {
26. xlinqDb.SqlQuery<User>(sql, dict);
27. }
28. });
29. ExecuteTimer("XLinq多表", () =>
30. {
31. for (int i = 0; i < count; i++)
32. {
33. (from user in userDB
34. join userRole in userRoleDb on user.Id equals userRole.UserId into us
35. from u in us.DefaultIfEmpty()
36. select user).Take(rowCount).ToList();
37. }
38. });
39. efDb = GetEF(efDb);
40. efUserDb = efDb.Users;
41. efUserRoleDb = efDb.UserRoles;
42. ExecuteTimer("EF使用LINQ", () =>
43. {
44. for (int i = 0; i < count; i++)
45. {
46. efUserDb.Take(rowCount).ToList();
47. }
48. });
49. efDb = GetEF(efDb);
50. efUserDb = efDb.Users;
51. efUserRoleDb = efDb.UserRoles;
52. ExecuteTimer("EF使用SQL", () =>
53. {
54. var sql = "select top " + rowCount + " * from dbo.users";
55. for (int i = 0; i < count; i++)
56. {
57. efDb.Database.SqlQuery<User>(sql).ToList();
58. }
59. });
60. efDb = GetEF(efDb);
61. efUserDb = efDb.Users;
62. efUserRoleDb = efDb.UserRoles;
63. ExecuteTimer("EF多表", () =>
64. {
65. for (int i = 0; i < count; i++)
66. {
67. (from user in efUserDb
68. join userRole in efUserRoleDb on user.Id equals userRole.UserId into us
69. from u in us.DefaultIfEmpty()
70. select user).Take(rowCount).ToList();
71. }
72. });
73. SqlConnection conn = new SqlConnection();
74. conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[1].ConnectionString;
75. ExecuteTimer("Dapper", () =>
76. {
77. var sql = "select top " + rowCount + " * from dbo.users";
78. for (int i = 0; i < count; i++)
79. {
80. conn.Query<User>(sql);
81. }
82. });
83. Console.ReadKey();
84. }
86. static void ExecuteTimer(string name, Action action)
87. {
88. var watcher = Stopwatch.StartNew();
89. action();
90. watcher.Stop();
91. Console.WriteLine(string.Format("{0}查询{1}数据{2}次", name, rowCount, count).PadRight(30) + ":" + watcher.Elapsed);
92. }
94. static TestDbContext GetEF(TestDbContext ef)
95. {
96. if (ef != null)
97. {
98. ef.Dispose();
99. }
100. TestDbContext efDb = new TestDbContext();
101. efDb.Users.Where(x => false).ToList();
102. efDb.Configuration.AutoDetectChangesEnabled = false;
103. efDb.Configuration.ProxyCreationEnabled = false;
104. efDb.Configuration.ValidateOnSaveEnabled = false;
105. return efDb;
106. }
108. public class TestDbContext : DbContext
109. {
110. public TestDbContext()
111. : base("name=test")
112. {
114. }
116. public System.Data.Entity.DbSet<User> Users { get; set; }
117. public System.Data.Entity.DbSet<UserRole> UserRoles { get; set; }
118. }
119. }
测试结果

查询一次的情况下EF慢的妥妥的，XLinq微弱优势

查询N次的情况下EF这···还有人说EF慢？不过大概是因为缓存的原因，但最后一张图看着又不太像，或许还跟GC什么的关吧，所以还有人说EF慢么··

或者是我测试代码有问题？
插入
1. class Program
2. {
3. static int count = 10;
4. static int rowCount = 1000;
5. static void Main(string[] args)
6. {
7. TestDataContext xlinqDb = new TestDataContext();
8. TestDbContext efDb = GetEF(null);
9. var userDB = xlinqDb.Set<User>();
10. var userRoleDb = xlinqDb.Set<UserRole>();
11. var efUserDb = efDb.Users;
12. var efUserRoleDb = efDb.UserRoles;
13. ExecuteInsertTimer("XLinq", () =>
14. {
15. for (int i = 0; i < rowCount; i++)
16. {
17. userDB.Add(new User()
18. {
19. C1 = "x",
20. C2 = "x",
21. C3 = "x",
22. C4 = "x",
23. C5 = "x",
24. C6 = "x",
25. C7 = "x",
26. C8 = "x",
27. C9 = "x",
28. C10 = "x",
29. Username = "xxxx"
30. });
31. }
32. xlinqDb.SaveChanges();
33. });
34. ExecuteInsertTimer("EF", () =>
35. {
36. for (int i = 0; i < rowCount; i++)
37. {
38. efUserDb.Add(new User()
39. {
40. C1 = "x",
41. C2 = "x",
42. C3 = "x",
43. C4 = "x",
44. C5 = "x",
45. C6 = "x",
46. C7 = "x",
47. C8 = "x",
48. C9 = "x",
49. C10 = "x",
50. Username = "xxxx"
51. });
52. }
53. efDb.SaveChanges();
54. });
55. Console.ReadKey();
56. }
58. static void ExecuteTimer(string name, Action action)
59. {
60. var watcher = Stopwatch.StartNew();
61. action();
62. watcher.Stop();
63. Console.WriteLine(string.Format("{0}查询{1}数据{2}次", name, rowCount, count).PadRight(30) + ":" + watcher.Elapsed);
64. }
65. static void ExecuteInsertTimer(string name, Action action)
66. {
67. var watcher = Stopwatch.StartNew();
68. action();
69. watcher.Stop();
70. Console.WriteLine(string.Format("{0}插入{1}条数据", name, rowCount).PadRight(30) + ":" + watcher.Elapsed);
71. }
73. static TestDbContext GetEF(TestDbContext ef)
74. {
75. if (ef != null)
76. {
77. ef.Dispose();
78. }
79. TestDbContext efDb = new TestDbContext();
80. efDb.Users.Where(x => false).ToList();
81. efDb.Configuration.AutoDetectChangesEnabled = false;
82. efDb.Configuration.ProxyCreationEnabled = false;
83. efDb.Configuration.ValidateOnSaveEnabled = false;
84. return efDb;
85. }
87. public class TestDbContext : DbContext
88. {
89. public TestDbContext()
90. : base("name=test")
91. {
93. }
95. public System.Data.Entity.DbSet<User> Users { get; set; }
96. public System.Data.Entity.DbSet<UserRole> UserRoles { get; set; }
97. }
98. }
测试结果