CODEWITHSUNDEEP
c#entity-frameworklinqef-fluent-api
Suamere
Suamere

Reputation: 6258

Linq To Entities Indirect Contextual Table Join in Fluent API

I am using pure Fluent API Code-First. I do not use Attributes on my Entities, and I keep my contexts seperate. I have an OrderEntity which has a One To Many relationship to OrderDetails, joined by OrderID. Therefore, I have An EntityTypeConfiguration<OrderDetailEntity> which has the following:

HasRequired(x => x.Order).WithMany(x => x.Details);
HasRequired(x => x.Item);

An Item has a relationship with its Prices. But the relationship is a Cartesian. One Item has 200+ ItemPrices associated with it. ItemPrice looks like this:

public string CurrencyCode { get; set; }
public int PriceTypeID { get; set; }
public decimal Price { get; set; }

There are 3 PriceTypes, and dozens of Currencies.

I consider it appropriate for ItemPrice to define the relationship between Item and ItemPrice like so:

HasRequired(x => x.Item).WithMany(x => x.ItemPrices);

An Item Does Not have a Currency Code or Price Type. An Item is just an item, and the Currency Code and Price Type are determined at the time the Order is placed. Those two variables are on the Order. So, to determine the price of an order, I have this:

SQL:

SELECT *
FROM Orders o
INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
INNER JOIN Items item ON od.ItemID = item.ItemID
INNER JOIN ItemPrices itemPrice ON item.ItemID = itemPrice.ItemID AND o.CurrencyCode = itemPrice.CurrencyCode AND o.PriceTypeID = itemPrice.PriceTypeID

And Linq:

context.Orders
.Join(context.OrderDetails,
    o => o.OrderID,
    od => od.OrderID,
    (o, od) => new { o, od })
.Join(context.Items,
    o2od => o2od.od.ItemID,
    item => item.ItemID,
    (o2od, item) => new { o2od, item })
.Join(context.ItemPrices,
    o2od2item => new { o2od2item.item.ItemID, o2od2item.o2od.o.CurrencyCode, o2od2item.o2od.o.PriceType },
    itemPrice => new { itemPrice.ItemID, itemPrice.CurrencyCode, itemPrice.PriceType },
    (o2od2item, itemPrice) => new { o2od2item, itemPrice })

Long-Story Short: There is an indirect relationship between the Order and the ItemPrice, in the context regarding calculating an Order's price.

All of my other LINQ is beautiful and using the navigation properties correctly and such. Is there a way to define this contextual relationship in Fluent API and use it when my LINQ has Order -> Detail -> Item -> Price ? Or do I have to do a manual definition of the relationship every time?

Upvotes: 1

Views: 647

Answers (1)

Suamere
Suamere

Reputation: 6258

Applying my SQL brain to Linq, I remember that most simple Joins can be moved into the WHERE Clause. So while this doesn't help to define the contextual relationship between Order and ItemPrices in Fluent API, it does allow me to use clean Linq without Joins like so:

context.Orders
.Where(x => orderIDs.Contains(x.OrderID)
    && x.Details.Any(y => 
        y.Item.ItemPrices.Any(z =>
            && z.CurrencyCode == x.CurrencyCode 
            && z.PriceType == x.PriceType
    )))

While I would rather describe the relationship between tables in different contexts, this LINQ Query fulfills my need to rely on navigation properties instead of joins.

Upvotes: 2

Related Questions