LINQ: Query two tables in one query

Question

A simple entity framework DbContext:

class SchoolDbContext : DbContext
{
    public DbSet<Teacher> Teachers {get; set;}
    public DbSet<Student> Students {get; set;}
}

Requirement Give me the number of Teachers and the number of Students.

Or, a little bit more challenging: "Give me the names of all Teachers and give me the birthdays of all Students"

Normally it would take two queries to do this. According to the following answers it is possible to do this using SQL in one query:

Answers are like:

SELECT id, startdate, enddate , '-' AS point_id 
FROM sarcshiftcentertable 
UNION
SELECT id, startdate, enddate, point_id 
FROM sarcshiftpointtable

But is it possible to do something similar using LINQ in one query?

The answers point me in the direction of Queryable.Union. However, this function expects for both collections the same input type.

So I should select something in common before the union, which in this case is object

var result = dbContext.Teachers.Select(teacher => teacher.Name).Cast<object>()
    .Union(dbContext.Students.Select(student => student.BirthDay).Cast<object>())
    // TODO select?

Now every DateTime in the sequence is a Students birthday. Every string is a Teachers name.

Another possibility is to select a new class:

var result = dbContext.Teachers.Select(teacher => new
{
    TeacherName = teacher.Name,
    StudentBirthDay = null,
})
.Union(dbContext.Students.Select(student => new
{
     TeacherName = null,
     StudentBirthDay = student.BirthDay,
}))
// TODO: select?

By now, I can detect whether an Item is a TeacherName, or a StudentBirthDay by checking whether the other property equals null.

But how to continue? How do I get one object like:

new
{
    TeacherNames = ...
    StudentBirthdays = ...
}

or

new
{
    TeacherCount = ...
    StudentCount = ...
}

Have a look at this answer: https://stackoverflow.com/questions/12339737/linq-select-from-multiple-tables — Dimitri, Mar 20 '19 at 14:28

Fabio · Answer 1 · 2019-03-20T10:01:20.257

0

With asynchronous approach it could be much easy task and probably more efficient

async Task<IEnumerable<string>> GetTeacherNames()
{
    using (var context = CreateContext(fromConfiguration))
    {
        return await context.Teachers.Select(teacher => teacher.Name).ToListAsync();
    }
}

async Task<IEnumerable<DateTime>> GetStudentsBirthdays()
{
    using (var context = CreateContext(fromConfiguration))
    {
        return await context.Students.Select(student => student.BirthDay).ToListAsync();
    }
}

Usage

var teacherTask = GetTeacherNames();
var studentsTask = GetStudentsBirthdays();

await Task.WhenAll(teacherTask, studentsTask);

var allTeacherNames = teacherTask.Result;
var allStudentsBirthdays = studentsTask.Result;

If teachers query takes for example 2 seconds and students 3 seconds, two asynchronous queries will take approximately 3 seconds.

edited Mar 20 '19 at 10:01

answered Mar 20 '19 at 09:53

Fabio

31,528
4
33
72

Although this works, it will still be two separate database calls, with the danger that the database has been changed between two calls, or you have to lock the database for a while – Harald Coppoolse Apr 11 '19 at 06:52
Why it would be danger? Queries are independent of each other – Fabio Apr 11 '19 at 09:44
With the simplified example of Teachers and Students, if won't be a problem, but if you ask for something related, for instance "give me the names of cities of all students, and the names of the cities of students that are taught by Professor Jonhson", then between the first and the second query, Professor Johnson could have acquired a new Student in a new City. Suddenly Professor Johnson has a Student living in a city that is not a city in the list of all cities of all students – Harald Coppoolse Apr 11 '19 at 09:56
For the case above, you will be able to write one query and return two columns `city name`, `is taught by Professor Johnson`, then in code create two city names collection based on second column `true/false` value. – Fabio Apr 11 '19 at 10:09

LINQ: Query two tables in one query

1 Answers1