How to query data with Filters from multiple tables in EF

Question

How i can query and filter data from multiple tables using EF with LINQ.

My classes:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; }
    public int Age { get; set; }
    public List<Character> Characters { get; set; }
}

public class Character
{
    public int Id { get; set; }
    public string Name { get; set; }
    [JsonIgnore]
    public User user { get; set; }
    public int UserId { get; set; }
}

1 user can have many characters. i need LINQ C# code like SQL:

select * from Users usr
join Character ch on usr.id = ch.UserId
where usr.Name == "String" 
or usr.Email == "String"
or ch.Name == "String"

I tried .Include.Where(), but didn’t fully understand how it works. I would like to see it with an example.

Answer 1

You can query and filter data from multiple tables using Entity Framework (EF) with LINQ by performing a join operation and applying filtering criteria. In your case, you want to retrieve data from both the User and Character tables based on certain conditions. Here's how you can do it using LINQ:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

// Your DbContext class that represents your database context
public class YourDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Character> Characters { get; set; }

    // Your DbContext configuration here...
}

// ...

// Inside your application logic or method
public List<User> GetFilteredUsers(string filterString)
{
    using (var dbContext = new YourDbContext())
    {
        var query = from user in dbContext.Users
                    join character in dbContext.Characters
                    on user.Id equals character.UserId
                    where user.Name.Contains(filterString) ||
                          user.Email.Contains(filterString) ||
                          character.Name.Contains(filterString)
                    select user;

        return query.ToList();
    }
}

Create an instance of your DbContext class (YourDbContext in this case) to connect to your database.

Use LINQ to perform a join operation between the Users and Characters tables based on the UserId relationship.

Apply filtering criteria using the where clause to select users whose name, email, or character name contains the specified filter string.

Finally, call .ToList() to execute the query and retrieve the filtered users as a list.

You can call the GetFilteredUsers method with a filter string as an argument to retrieve users that match your criteria. Adjust the filtering conditions as needed to suit your specific requirements.

Remember to configure your DbContext and connection string appropriately to establish a connection to your database.

If you need to perform a LEFT JOIN instead of an INNER JOIN in your LINQ query, you can achieve that by using the join into clause and a subsequent DefaultIfEmpty() method. Here's how you can modify the LINQ query to perform a LEFT JOIN:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

// Your DbContext class that represents your database context
public class YourDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Character> Characters { get; set; }

    // Your DbContext configuration here...
}

// ...

// Inside your application logic or method
public List<User> GetFilteredUsers(string filterString)
{
    using (var dbContext = new YourDbContext())
    {
        var query = from user in dbContext.Users
                    join character in dbContext.Characters
                    on user.Id equals character.UserId into characterGroup
                    from cg in characterGroup.DefaultIfEmpty()
                    where user.Name.Contains(filterString) ||
                          user.Email.Contains(filterString) ||
                          (cg != null && cg.Name.Contains(filterString))
                    select user;

        return query.ToList();
    }
}

We perform a standard join operation between the Users and Characters tables.

We use the into clause to group the join results into a characterGroup collection.

We then use the from clause with DefaultIfEmpty() to perform a LEFT JOIN. This ensures that all records from the left table (Users) are included in the result, even if there is no corresponding record in the right table (Characters).

We apply the filtering conditions as before, making sure to check if cg (the character) is not null before applying the filter based on character name.

This query will give you the result you need with a LEFT JOIN between the Users and Characters tables, ensuring that all users are included in the result, regardless of whether they have associated characters.

Entity Framework Core allows you to express complex queries in a more concise and fluent syntax using LINQ. The syntax you mentioned, which combines Include with Where, is a powerful way to load related data and apply filtering in a readable manner.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

// Your DbContext class that represents your database context
public class YourDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Character> Characters { get; set; }

    // Your DbContext configuration here...
}

// ...

// Inside your application logic or method
public List<User> GetFilteredUsers(string filterString)
{
    using (var dbContext = new YourDbContext())
    {
        var query = dbContext.Users
            .Include(u => u.Characters)  // Include the related Characters
            .Where(u => u.Name.Contains(filterString) ||
                        u.Email.Contains(filterString) ||
                        u.Characters.Any(c => c.Name.Contains(filterString)));

        return query.ToList();
    }
}

In this syntax:

We start with dbContext.Users, indicating that we want to query the Users table.

We use .Include(u => u.Characters) to eagerly load the related Characters for each user. This allows you to access the user's characters directly in your query.

We then apply the filtering criteria using.Where(), checking for conditions within the Users table as well as within the related Characters using .Characters.Any().