Q.yuhen

以下代码中皆以 User Entity 作演示，注意替换。

1. 获取实体对应数据表名

方法1

string tableName = context.Mapping.GetTable(typeof(User)).TableName;
Console.WriteLine(tableName);

方法2

var attrs = typeof(User).GetCustomAttributes(typeof(TableAttribute), true) as TableAttribute[];
if (attrs != null && attrs.Length > 0)
{
    var tableName = attrs[0].Name;
    Console.WriteLine(tableName);
}

2. 删除全部实体

不要试图用下面的方法，这会导致 N 条 Delete 语句被执行。

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;

    context.Users.RemoveAll(from u in context.Users select u);
    context.SubmitChanges();
}

合适的做法应该是调用 ExecuteCommand。

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    string sql = String.Format("delete from {0}", context.Mapping.GetTable(typeof(User)).TableName);
    context.ExecuteCommand(sql);
}

3. 批量执行

当我们批量执行多个操作时，最好显示打开数据库连接。我们看看下面两种方法的区别。

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Connection.StateChange += 
        (o, s) => Console.WriteLine("Original:{0} -> CurrentState:{1}", s.OriginalState, s.CurrentState);

    context.ExecuteCommand(String.Format("delete from {0}", 
        context.Mapping.GetTable(typeof(User)).TableName));

    context.Users.Add(new User { Name = "user1", Age = 1 });
    context.Users.Add(new User { Name = "user2", Age = 1 });
    context.Users.Add(new User { Name = "user3", Age = 1 });
    context.SubmitChanges();
     
    var result = from u in context.Users select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

输出
Original:Closed -> CurrentState:Open
Original:Open -> CurrentState:Closed
Original:Closed -> CurrentState:Open
Original:Open -> CurrentState:Closed
Original:Closed -> CurrentState:Open
Original:Open -> CurrentState:Closed
Original:Closed -> CurrentState:Open
user1
user2
user3
Original:Open -> CurrentState:Closed

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Connection.StateChange += 
        (o, s) => Console.WriteLine("Original:{0} -> CurrentState:{1}", s.OriginalState, s.CurrentState);

    context.Connection.Open();
    try
    {
        context.ExecuteCommand(String.Format("delete from {0}", 
            context.Mapping.GetTable(typeof(User)).TableName));

        context.Users.Add(new User { Name = "user1", Age = 1 });
        context.Users.Add(new User { Name = "user2", Age = 1 });
        context.Users.Add(new User { Name = "user3", Age = 1 });
        context.SubmitChanges();

        var result = from u in context.Users select u;
        foreach (var user in result)
        {
            Console.WriteLine(user.Name);
        }
    }
    finally
    {
        context.Connection.Close();
    }
}

输出
Original:Closed -> CurrentState:Open
user1
user2
user3
Original:Open -> CurrentState:Closed

很显然，显示打开数据库连接，能在一定程度上提高性能。

4. 分页返回

利用 SQL Server 2005 新增特性，总算解决了以往的老大难。

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    int pageIndex = 2;
    int pageSize = 10;

    int skipCount = (pageIndex - 1) * pageSize;

    var result = (from u in context.Users orderby u.Age select u).Skip(skipCount).Take(pageSize);
    foreach (var user in result)
    {
        Console.WriteLine("{0}={1}", user.Name, user.Age);
    }
}

输出 SQL

SELECT TOP 10 [t1].[Id], [t1].[Name], [t1].[Age]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Age]) AS [ROW_NUMBER], [t0].[Id], [t0].[Name], [t0].[Age]
    FROM [dbo].[User] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
ORDER BY [t1].[Age]
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

如果是其他版本数据库，估计还得自己想折，不过好在 DLinq 调用存储过程也很方便。

5. 通配符查询

(1) Like '%user1%'

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;
    
    var result = from u in context.Users where u.Name.Contains("user1") select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

SQL

SELECT [t0].[Id], [t0].[Name], [t0].[Age]
FROM [dbo].[User] AS [t0]
WHERE [t0].[Name] LIKE @p0
-- @p0: Input String (Size = 7; Prec = 0; Scale = 0) [%user1%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

(2) Like '%1'

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;

    var result = from u in context.Users where u.Name.EndsWith("1") select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

SQL

SELECT [t0].[Id], [t0].[Name], [t0].[Age]
FROM [dbo].[User] AS [t0]
WHERE [t0].[Name] LIKE @p0
-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [%1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

(3) Like 'user1%'

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;

    var result = from u in context.Users where u.Name.StartsWith("user1") select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

SQL

SELECT [t0].[Id], [t0].[Name], [t0].[Age]
FROM [dbo].[User] AS [t0]
WHERE [t0].[Name] LIKE @p0
-- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [user1%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

(4) Not Like 'user1%'

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    var result = from u in context.Users where !u.Name.StartsWith("user1") select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

SQL

SELECT [t0].[Id], [t0].[Name], [t0].[Age]
FROM [dbo].[User] AS [t0]
WHERE NOT ([t0].[Name] LIKE @p0)
-- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [user1%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

(5) Like 'user_1'

这次我们得借助 System.Data.Linq.SqlClient.SqlMethods。SqlMethods.Like 支持在任意位置使用数据库支持的任何通配符。

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;

    var result = from u in context.Users where SqlMethods.Like(u.Name, "user_1") select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

SQL

SELECT [t0].[Id], [t0].[Name], [t0].[Age]
FROM [dbo].[User] AS [t0]
WHERE [t0].[Name] LIKE @p0
-- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [user_1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

附：SQL Server 通配符

• % : 零个或多个任意字符。
• _ : 任意一个字符。
• [ ] : 此范围内的任意一个字符，如 [a-z]、[abcdefg] 等。
• [^] : 不在此范围内的任意一个字符。如 [^a-z]、[^abcdefg] 等。

如果搜索字符串中包含上述通配字符，那么我们需要添加转义字符。比如，我们搜索所有 Name 以 "test%user" 开头的记录。

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;

    var result = from u in context.Users where SqlMethods.Like(u.Name, @"test\%user%", '\\')  select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

SQL

SELECT [t0].[Id], [t0].[Name], [t0].[Age]
FROM [dbo].[User] AS [t0]
WHERE [t0].[Name] LIKE @p0 ESCAPE @p1
-- @p0: Input String (Size = 11; Prec = 0; Scale = 0) [test\%user%]
-- @p1: Input String (Size = 1; Prec = 0; Scale = 0) [\]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

6. 范围查询

using (DataClasses1DataContext context = new DataClasses1DataContext())
{
    context.Log = Console.Out;

    var users = new string[] { "user2", "user4", "uSer5" };
    var result = from u in context.Users where users.Contains(u.Name) select u;
    foreach (var user in result)
    {
        Console.WriteLine(user.Name);
    }
}

SQL

SELECT [t0].[Id], [t0].[Name], [t0].[Age]
FROM [dbo].[User] AS [t0]
WHERE [t0].[Name] IN (@p0, @p1, @p2)
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [user2]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [user4]
-- @p2: Input String (Size = 5; Prec = 0; Scale = 0) [uSer5]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1