T-SQL to LINQ to SQL using Navigation Properties
Clash Royale CLAN TAG#URR8PPPT-SQL to LINQ to SQL using Navigation Properties
I can’t seem to come up with the right corresponding LINQ to SQL statement to generate the following T-SQL. Essentially, I'm trying to return payment information with only one of the customer's addresses... the AR address, if it exists, then the primary address, if it exists, then any address.
SELECT < payment and address columns >
FROM Payment AS p
INNER JOIN Customer AS c ON c.CustomerID = p.CustomerID
OUTER APPLY (
SELECT TOP 1 < address columns >
FROM Address AS a
WHERE a.person_id = c.PersonID
ORDER BY CASE WHEN a.BusinessType = 'AR' THEN 0
ELSE 1
END
, a.IsPrimary DESC
END
) AS pa
WHERE p.Posted = 1
We’re using the Repository Pattern to access the DB, so inside a method of the Payment Repository, I’ve tried:
var q = GetAll()
.Where(p => p.Posted == true)
.SelectMany(p => p.Customer
.Address
.OrderBy(a => a.BusinessType != "AR")
.ThenBy(a => a.Primary != true)
.Take(1)
.DefaultIfEmpty()
.Select(a => new
{
< only the columns I need from p and a >
});
But when I execute .ToList(), it throws the NullReferenceException (Object reference not set to an instance of an object) on a record where the customer has no addresses set up. So, I tried:
.ToList()
NullReferenceException
var q1 = GetAll().Where(p => p.Posted == true);
var q2 = q11.SelectMany(p => p.Customer
.Address
.OrderBy(a => a.BusinessType != "AR")
.ThenBy(a => a.Primary != true));
var q3 = q1.SelectMany(p => q2.Where(a => a.PersonID == p.Customer.PersonID)
.Take(1)
.DefaultIfEmpty()
.Select(a => new
{
< only the columns I need from p and a >
});
This returns the correct results, but the T-SQL it generates puts the entire T-SQL from above into the outer apply, which is then joined again on Payment and Customer. This seems somewhat inefficient and I wondered if it could be made more efficient because the T-SQL above returns in 6ms for the test case I’m using.
Payment
Customer
Additional Info:
Q: I think the problem here is that GetAll() returns IEnumerable, not IQueryable ... it would help to see this GetAll() method. - Gert Arnold
A: Actually, GetAll() simply returns the result of this.Query().Where(x => x.Blah == Blah), so it does return IQueryable.
Since Address represents a 0:m relationship, I thought I needed to use SelectMany to essentially flatten out that collection and then return only the first one based on the ordering. It would also allow me to return columns for both payments and addresses, whereas Select would only return Address information. If I'm wrong, please feel free to educate me.
– DeuceyPoo's Dad
2 hours ago
It looks like you are correct. I would use .FirstOrDefault() instead of .Take(1).DefaultIfEmpty. Take returns an array object even if you have only one item. So you would have SelectMany(p => p.Where(a => a.PersonID == p.Customer.PersonID).FirstOrDefault()).Select(a =>new ......)ToList();. FirstOrDefault gives you a singleton while Take(1) still gives you an array.
– jdweng
2 hours ago
I think the problem here is that
GetAll() returns IEnumerable, not IQueryable, so the query can't be translated into SQL (which isn't null-reference sensitive). It would help to see this GetAll() method.– Gert Arnold
53 mins ago
GetAll()
IEnumerable
IQueryable
GetAll()
@GertArnold - See updated question above. HA! I can’t fully answer that question without getting into (even more) trouble, but you’ve just been added to the quotes I’ve collected I show my boss occasionally to justify using EF (as if it needs justification).
– DeuceyPoo's Dad
2 mins ago
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Why are you using SelectMany instead of just Select?
– jdweng
3 hours ago