LINQ to SQL系列四 使用inner join,outer join

本文中涉及的表数据模型请参考上一篇文章

先看一个最简单的inner join,在读取Student表时inner join Class表取的对应的Class信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
static void Main(string[] args)
{
    using (var writer = new StreamWriter(WatchSqlPath, false, Encoding.UTF8))
    {
        using (DbAppDataContext db = new DbAppDataContext())
        {
            db.Log = writer;
 
            //inner join
            var query = from s in db.Students
                        join c in db.Classes on s.ClassID equals c.ClassID
                        where c.ClassID == 1
                        select new
                        {
                            ClassID = s.ClassID,
                            ClassName = c.ClassName,
                            Student = new
                            {
                                Name = s.Name,
                                ID = s.StudentID
                            }
                        };
 
            foreach (var item in query)
            {
                Console.WriteLine("{0} {1} {2}", item.ClassID, item.ClassName, item.Student.Name);
            }
        }
    }
 
    Console.ReadLine();
}

特别简单,就不贴sql了,继续看outer join:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
static void Main(string[] args)
{
    using (var writer = new StreamWriter(WatchSqlPath, false, Encoding.UTF8))
    {
        using (DbAppDataContext db = new DbAppDataContext())
        {
            db.Log = writer;
 
            //left outer join
            var query = from s in db.Students
                        join c in db.Classes on s.ClassID equals c.ClassID into gc
                        from gci in gc.DefaultIfEmpty()
                        select new
                        {
                            ClassID = s.ClassID,
                            ClassName = gci.ClassName,
                            Student = new
                            {
                                Name = s.Name,
                                ID = s.StudentID
                            }
                        };
            foreach (var item in query)
            {
                Console.WriteLine("{0} {1} {2}", item.ClassID, item.ClassName, item.Student.Name);
            }
        }
    }
 
    Console.ReadLine();
}

Outer join时必须将join后的表into到一个新的变量gc中,然后要用gc.DefaultIfEmpty()表示外连接。

linq to sql相关随笔:

1.  从CUD开始,如何使用LINQ  to SQL插入、修改、删除数据

2.  查询 使用LINQ to SQL做简单查询

3.  查询 延迟加载与立即加载,使用LoadWith和AssociateWith

4.  查询 inner join,left outer join

5.  Linq to SQL中的聚合grouping having

6.  LINQ to SQL查询优化,需要忧虑性能吗?

posted @   玉开  阅读(23141)  评论(4编辑  收藏  举报
编辑推荐:
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
阅读排行:
· 手把手教你更优雅的享受 DeepSeek
· AI工具推荐:领先的开源 AI 代码助手——Continue
· 探秘Transformer系列之(2)---总体架构
· V-Control:一个基于 .NET MAUI 的开箱即用的UI组件库
· 乌龟冬眠箱湿度监控系统和AI辅助建议功能的实现
历史上的今天:
2007-05-17 备忘-链接执行js时注意target必须是_self或者_top
点击右上角即可分享
微信分享提示