Entity Framework Core Select Outer Join

Question

is there an easy way to include a nullable navigation inside a select expression for EF Core?

My model looks like this

public class RootVO
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(200)]
    public string Description { get; set; }

    public int? RelationId { get; set; }
    [ForeignKey(nameof(RelationId))]
    public RelationVO Relation { get; set; }
}

public class RelationVO
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(200)]
    public string Property1 { get; set; }

    [Required]
    [StringLength(200)]
    public string Property2 { get; set; }

    public ICollection<RootVO> RootRelations { get; set; }
}

When I load the data I just want to select certain kind of properties. Currently my expression looks like this:

Expression<Func<RootVO, RootVO>> selectExpr = m => new RootVO
{
    Id = m.Id,
    Description = m.Description,
    Relation = m.Relation != null ? new RelationVO
    {
        Id = m.Relation.Id,
        Property1 = m.Relation.Property1                        
    } : null
};

var result = context.Roots.Select(selectExpr).ToList();

Is there an easier way to handle the relation select?

Edit

Maybe some background here will help:

I have a huge object with a lot of columns and relations, some with inner, some with outer joins. This query gets accessed by a datagrid on UI which can have dynamic columns depending on the user selection. To increase the performance I've written a class that will build the select expression dynamicly depending on the selected columns. For now it is working, but I'm having trouble when an outer join is null due to null-reference excepction.

The debug view on the expression could look like this:

.New IVA.Core.Data.Models.StockMovementLogVO(){
    SequenceNo = $m.SequenceNo,
    PostingPeriodId = $m.PostingPeriodId,
    TransactionDate = $m.TransactionDate,
    FinancialYear = $m.FinancialYear,
    FinancialYearPeriod = $m.FinancialYearPeriod,
    VoucherDate = $m.VoucherDate,
    ItemQuantity = $m.ItemQuantity,
    BuCode = $m.BuCode,
    LocationStructure = .New IVA.Core.Data.Models.LocationStructureVO(){
        Id = ($m.LocationStructure).Id,
        Description = ($m.LocationStructure).Description
    },
    BookingType = .New IVA.Core.Data.Models.BookingTypeVO(){
        Id = ($m.BookingType).Id,
        Description = ($m.BookingType).Description
    },
    PartnerStockLocationType = .New IVA.Core.Data.Models.StockLocationTypeVO(){
        Id = ($m.PartnerStockLocationType).Id,
        Description = ($m.PartnerStockLocationType).Description
    },
    StockLocationType = .New IVA.Core.Data.Models.StockLocationTypeVO(){
        Id = ($m.StockLocationType).Id,
        Description = ($m.StockLocationType).Description
    }
}

StockLocationType and PartnerStockLocationType are outer joins and if those are null the query fails to execute.

What is easiest way means? Have you tried Select with the above without passing Expressions? — SelvaS
Why not just query context.Roots and Include(r => r.Relation)? — Gert Arnold
I just want to load certrain properties of the relation object and not all, so I have to create my own select statement. — NiQu
@NiQu Anyway the EF will use JOIN with the second table. We can do this without expression using Select(x => new RootVo {}). Do you expect any other kind of solution? — SelvaS
I've extended the description. Maybe this will help a little bit to understand my problem. — NiQu

NiQu NiQu · Accepted Answer · 2018-07-11T09:12:20

I've now changed my expression builder that it will take care of the outer joins by including a null reference check. The expression now looks like this:

.New IVA.Core.Data.Models.StockMovementLogVO(){
    SequenceNo = $m.SequenceNo,
    PostingPeriodId = $m.PostingPeriodId,
    TransactionDate = $m.TransactionDate,
    FinancialYear = $m.FinancialYear,
    FinancialYearPeriod = $m.FinancialYearPeriod,
    VoucherDate = $m.VoucherDate,
    ItemQuantity = $m.ItemQuantity,
    BuCode = $m.BuCode,
    LocationStructure = .New IVA.Core.Data.Models.LocationStructureVO(){
        Id = ($m.LocationStructure).Id,
        Description = ($m.LocationStructure).Description
    },
    BookingType = .New IVA.Core.Data.Models.BookingTypeVO(){
        Id = ($m.BookingType).Id,
        Description = ($m.BookingType).Description
    },
    PartnerStockLocationType = .If ($m.PartnerStockLocationType != null) {
        .New IVA.Core.Data.Models.StockLocationTypeVO(){
            Id = ($m.PartnerStockLocationType).Id,
            Description = ($m.PartnerStockLocationType).Description
        }
    } .Else {
        null
    },
    StockLocationType = .If ($m.StockLocationType != null) {
        .New IVA.Core.Data.Models.StockLocationTypeVO(){
            Id = ($m.StockLocationType).Id,
            Description = ($m.StockLocationType).Description
        }
    } .Else {
        null
    }
}

Edit

If anyone is interessted how it looks, I've created a repository where I use the class.

https://github.com/NQuirmbach/DynamicQueryBuilder

Entity Framework Core Select Outer Join

1 Answers