代码改变世界

.NET深入实战系列—Linq to Sql进阶

2015-08-30 22:55 by 李玉宝, ... 阅读, ... 评论, 收藏, 编辑

最近在写代码的过程中用到了Linq查询,在查找资料的过程中发现网上的资料千奇百怪,于是自己整理了一些关于Linq中容易让人困惑的地方。

本文全部代码基于:UserInfo与Class两个表,其中Class中的UserId与UserInfo中的Id对应

image

 本文唯一访问地址:http://www.cnblogs.com/yubaolee/p/BestLinqQuery.html

linq联合查询

内联查询

内联是一个实际使用频率很高的查询,它查询两个表共有的且都不为空的部分

 from user in UserInfo
 join c in Classes on user.Id equals c.UserId
 select new
 {
     user.UserName,
     user.Id,
	 c.ClassName
 }

查询结果

image

对应的SQL语句

SELECT [t0].[UserName], [t0].[Id], [t1].[ClassName]
FROM [UserInfo] AS [t0]
INNER JOIN [Class] AS [t1] ON [t0].[Id] = [t1].[UserId]

左联查询

左联应该是联合查询中使用频率最高的查询。它以左表为准,进行联合查询。如果右表中不存在对应的结果,则置空。(注意:在Linq中是不存在右联连的说法,因为右联无非是把左边的表移动到右边,查询的结果与左联是一样的)

from user in UserInfo  
join c in Classes on user.Id equals c.UserId into temp  
from c in temp.DefaultIfEmpty()  
select new  
{  
     user.UserName,  
     user.Id,  
     c.ClassName  
}  

查询结果

image

对应SQL语句

SELECT [t0].[UserName], [t0].[Id], [t1].[ClassName] AS [ClassName]
FROM [UserInfo] AS [t0]
LEFT OUTER JOIN [Class] AS [t1] ON [t0].[Id] = [t1].[UserId]

!注意一下左联那个【temp】,它其实是一个IEnumerable集合。所以我们可以得到到左联的另一种结果:

from user in UserInfo
join c in Classes on user.Id equals c.UserId into temp
select new
{
	user,
	temp
}

查询结果(为了更明确表达集合,在Class表里特别加了一条记录,所以class那边共有3条)

image

对应SQL语句,与左联的SQL基本一样,但多了一个统计行数的列

SELECT t0.*, [t1].[Id] AS [Id2], t1.*, (
    SELECT COUNT(*)
    FROM [Class] AS [t2]
    WHERE [t0].[Id] = [t2].[UserId]
    ) AS [value]
FROM [UserInfo] AS [t0]
LEFT OUTER JOIN [Class] AS [t1] ON [t0].[Id] = [t1].[UserId]

全联接

全联连是得到两个表的交叉结果(在SQL中称为cross join),这种联连方式得到的结果在没有过滤条件的情况下,基本没什么用。看看即可,代码如下:

 from user in UserInfo
 from c in Classes
 select new
 {
     user.UserName,
     user.Id,
	 c.ClassName
 }

查询结果

image

对应SQL语句

SELECT [t0].[UserName], [t0].[Id], [t1].[ClassName]
FROM [UserInfo] AS [t0], [Class] AS [t1]

合并(Union)

这种查询其实也很少用,但在某些变态业务需求下会非常有用,注意查询的结果。它是合并两个表相同列数的结果,并且如果结果中有相同的行,那么只取一行记录。

(
	from userinfo in UserInfo
	select new {
	  Id = (System.Int32?)userinfo.Id,
	  Name = userinfo.UserName
	}
).Union
(
	from c in Classes
	  select new {
	  Id = (System.Int32?)c.UserId,
	  Name = c.ClassName
	}
)

查询结果

image

对应SQL语句

SELECT [t0].[Id] AS [value], [t0].[UserName]
FROM [UserInfo] AS [t0]
UNION
SELECT [t1].[UserId] AS [value], [t1].[ClassName]
FROM [Class] AS [t1]

Linq 分组查询

分组查询(group by)也是我们在实际项目中一个常用的操作,查询操作如下:

from c in Classes
group c by c.UserId into temp
select temp

查询结果

image

 

注意一下查询结果,外层是一个我们常用的IQueryable<T>,里面是一个类似Dictionary的K-V集合。简单点说Group返回的是一个集合的集合,因此输出时需用双重循环。

我们在使用它的结果时,应该这样调用:

 var result = from c in _context.Classes
                group c by c.UserId
                into temp
                select temp;

            foreach (var c in result)
            {
                Console.WriteLine(c.Key);
                foreach (var citem in c)
                {
                    Console.WriteLine(citem.ClassName);
                }
            }

实体增加字段处理

我在本文例子中的UserInfo实体类如下:

 public partial class UserInfo
    {
        public int Id { get; set; }
        public string UserName { get; set; }
        public string UserType { get; set; }
        public int Money { get; set; }
    }

现在我想在该实体中类中添加一个属性。为了保持原实体类的纯洁。我添加一个新的partial类:

public partial class UserInfo
    {
        /// <summary>
        /// 测试扩展属性
        /// </summary>
        public string UserExt
        {
            get { return UserName + ":" + UserType; }
        }
    }

然后我们用EF访问一下,发现是可以访问的:

image

但如果我们这样使用时:

from user in _context.UserInfoes
select new
{
    user.Id,
    user.UserExt
};

会发现编译是没有问题的。但运行时会出现下面异常:

image

具体错误信息如下: The specified type member 'UserExt' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

即"UserExt"类型并不能被linq支持。因为在进入到foreach进行真正取数据之前。EF已经把linq转成SQL语句,而UserExt会被转成对应的数据库字段。因为数据库中并没有该字段,所以会出现这个问题。解决的方法很简单:

from user in _context.UserInfoes.ToList()
select new  
{  
    user.Id,  
    user.UserExt  
}; 

即先执行ToList(),提前让linq进行执行,生成UserInfo集合,这样就可以正常访问UserExt了。别看这个小小的改动。在多表联查过滤字段的情况下,你会体会到无尽的妙处!

你可能会想到一个问题,如果我再加一个完整的属性会出现什么情况?

 public partial class UserInfo
    {
        public string UserExt
        {
            get { return UserName + ":" + UserType; }
        }
        //新增一个完整的属性
        public string UserExt2 { get; set; }
    }

上面的UserExt2是我们新加入的一个属性,现在我们来执行一下查询。我想真正去研究过Linq的人肯定知道结果了。

image

在Linq操作中实体中的属性必须在配置映射时指定。我们的数据库中当然没有UserExt2这个字段,所以增加Ignore标识,或调用一下:

this.Ignore(t => t.UserExt2);