Linq to SQL中的联合查询以选择所有记录，即使外键列中有一些空值 [英] Joint query in Linq to SQL to select all records even there are some null in the columns of foreign keys

问题描述

我想在C#或VB.NET中接受所有建议.

I'd like to take all suggestion either in C# or VB.NET.

我有一个数据库图，如下图.我还在这里包括数据库脚本@ http://pastebin.com/pDC25Gkm

I have a DB diagram like the image below. I also include the database script here @ http://pastebin.com/pDC25Gkm

在学生"表中，"CountryId"和"RoomId"列允许为空.因为某些记录还没有关于房间和国家的信息. 另外，有些学生没有论文.

In the Students table, CountryId and RoomId column are allowed null. Because some records do not have info about room and country yet. Also, some students do not have essays.

我正在对所有表进行联合查询.我想选择所有学生 像这样投影结果:

I'm doing a joint query with all tables. I want to select all students to project the result like this:

想要的查询结果.

这是我当前的查询，其结果如下图所示:

Here's my current query that gives the result like the image below:

    Dim db As New DBDataContext

    Dim query = From st In db.Students _
                Join c In db.Countries On c.Id Equals st.Id _
                Join r In db.Rooms On r.Id Equals st.RoomId _
                Join b In db.Buildings On b.Id Equals r.BuildingId _
                Join es In db.Essays On es.StudentId Equals st.Id _
                Select st, c, r, b, es

    'put query result into datatable
    Dim dt = New DataTable("Result")
    dt.Columns.Add("Id", GetType(Integer))
    dt.Columns.Add("Name", GetType(String))
    dt.Columns.Add("Room", GetType(String))
    dt.Columns.Add("Building", GetType(String))
    dt.Columns.Add("Country", GetType(String))
    dt.Columns.Add("Essay", GetType(String))

    For Each q In query

        dt.Rows.Add(New Object() {q.st.Id, q.st.FullName, q.r.RoomNumber, q.b.BuildingName, q.c.CountryName, q.es.Eassay})

    Next

    'bind to list view
    lvStudent.DataSource = dt
    lvStudent.DataBind()

当前查询结果

我只得到一个结果，因为我在每个表中都有(2)William NoMan记录.但是我对其他人一无所知，例如(3)Sync Master除了RoomId以外，什么都没有.

I got only one result back because I have (2) William NoMan record in every table. But I don't get anything about others, like (3) Sync Master who has everything but RoomId.

我需要对上面的查询进行哪些修改，以便像上面想要的查询图像中的所有学生一样给我所有学生?谢谢.

What do I need to modify the query above so it will give me all students like in the wanted query image above? Thank you.

推荐答案

我使用了以下查询:

    Dim query = From st In db.Students _
             From c In db.Countries.Where(Function(c) c.Id = st.CountryId).DefaultIfEmpty _
             From r In db.Rooms.Where(Function(r) r.Id = st.RoomId).DefaultIfEmpty _
             From b In db.Buildings.Where(Function(b) b.Id = r.BuildingId).DefaultIfEmpty _
             From es In db.Essays.Where(Function(es) es.StudentId = st.Id).DefaultIfEmpty _
             Select st.Id, st.FullName, c.CountryName, r.RoomNumber, b.BuildingName, es.Eassay

它给出了我想要的结果，但是我不确定这是否是很好的查询.一定有比这更好的方法.

It gives the result I wanted but I'm not sure if it's good query. There must be another way better than this.

这篇关于Linq to SQL中的联合查询以选择所有记录，即使外键列中有一些空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！