Add an inner or outer join based on a parameter

asp.net-core c# entity-framework-core join sql

Question

This is probably an easy/stupid question but is their a way to add an inner or outer join to a SQL query (with entity framework) based on data you receive?

Example

public bool method(int? typeId, int? categoryId){
    var query = from o in _dbContext.SomeObjects;

    //JOIN type
    if(typeId != null){
        //Add inner join with table types to query
        //Something like:  
        //query += join type in _dbContext.Types on o.TypeId equals type.ID
    }else{
        //Add outer join with table types to query
        //query += join type in _dbContext.Types on o.TypeId equals type.ID into types
        //             from type in types.DefaultIfEmpty()
    } 

    //Do same for category
    ...

    //Filters
    if(typeId != null){
        query += where type.ID == typeId
    }

    if(categoryId != null){
        query += where category.ID == categoryId
    }

}
1
0
2/19/2019 12:50:10 PM

Accepted Answer

Fixed it by doing the following:

var query = from o in _dbContext.SomeObjects
                join type in _dbContext.Types on o.TypeId equals type.ID
            where (typeId == null || type.ID == typeId) &&
0
2/19/2019 12:48:37 PM

Popular Answer

I think your main issue here is simply typing. Using var to store the initial query will type it as DbSet<SomeObject>. To build queries, you need IQueryable<SomeObject>. In other words, change your initial line to:

IQueryable<SomeObject> query = from o in _dbContext.SomeObjects;

I don't use LINQ-to-SQL myself, so this may be a little off, but I think then you'd just do something like:

query = query join type in _dbContext.Types on o.TypeId equals type.ID;

I know it works with LINQ-to-Entities like:

query = query.Include(x => x.Types);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow