CODEWITHSUNDEEP
c#.netsqlt-sqllinq-to-sql
Mikolaj Kieres
Mikolaj Kieres

Reputation: 4405

Translate t-sql query to LINQ (left join with subquery/nested right join)

Here's query that I've managed to write:

select *
from [Transaction] as t left join (ProductTransaction as pt right join Product as p on pt.productId = p.productId) on t.transactionId = pt.transactionId
                    left join (ReservationTransaction as rt right join Reservations as r on rt.reservationId = r.reservationId) on t.transactionId = rt.transactionId

So far I've got something like this (without nested right joins) and I'm stuck. Could anyone help me with these subquery/nested joins ?

from t in dataContext.Transactions
                join pt in dataContext.ProductTransactions on t.transakcja_id equals pt.transactionId into pTransactions
                from pt in pTransactions.DefaultIfEmpty()
                join rt in dataContext.ReservationTransactions on t.transakcja_id equals rt.transactionId into rTransactions
                from rt in rTransactions.DefaultIfEmpty()

EDIT: OK, thanks for the tips. What i'm trying to do is to combine ~5 entieties, which are related (they're connected with foreign keys), into one table. The case is I need all Transaction but not every transaction is connected to product or reservation. So I'm not sure if i'll be able to get all of them using simple associetion properties and much more queries than one or am I ? Maybe I should use more than one query and have the problem of my chest ?

I uploaded a screenshot of entieties scheme that I'm working on. Thanks in advance for your advices. part of db scheme <-- Couldn't upload on stackoverflow cause of my low reputation points

Upvotes: 0

Views: 351

Answers (1)

Guillaume86
Guillaume86

Reputation: 14400

Setup foreign keys in your database, you will not need joins, you'll access linked tables with association properties like that (just a exemple):

var transation = dataContact.Transations.First();

var price = transaction.ProductTransaction.Product.Price;

Upvotes: 1

Related Questions