Trouble with EF and OData: SQL Returns Incorrect Navigation Column Names

Data and Databases
1

Using EF POCO classes with OData expansion can wrongly add non-existent navigation columns in SQL. See revised sample code. How can unnecessary columns be omitted?

public class CustomerRecord {
    [Key]
    public Guid CustId { get; set; }
    public virtual ICollection<OrderRecord> Orders { get; set; }
}

public class OrderRecord {
    [Key]
    public Guid OrdId { get; set; }
    public Guid CustId { get; set; }
    [ForeignKey("CustId")]
    public virtual CustomerRecord Customer { get; set; }
}

var orderList = dbContext.Orders.ToList();
2

hey there, i’m wonderin if tryin an explicit select using anonymous types might work? seems like rough workaround. have u playd with modelbuilder config at all? curious if that helped u in any way or got any other tricks up ur sleeve.

3

hey, u could try a projection query to only pull the fields u need. that way, ef doesnt bring in the extra nav cols automatically. give that a shot!

4

In my experience, fine-tuning the mapping configuration in your DbContext can help mitigate the issue. Instead of relying solely on the default conventions, defining explicit mappings for navigation properties ensures that only the intended columns are generated. This can be achieved by using the Fluent API during the OnModelCreating phase. Additionally, when working with OData, using a custom DTO for data transfer can restrict the result set explicitly. These changes lead to improved control and prevent the inclusion of redundant navigation columns in the SQL query.

5

hey, had a similar issue. i ended up disabling lazy loading and using custom select queries. that way, ef stops auto adding unneeded nav cols. might be worth revisiting your context config to see if that suits your setup.

6

hey, maybe try a custom projection to pull only what u need? i reckon setting up a dto might help omit those extra cols. have u tried tweaking the config settings to better control the mapping? curious what u think!