LINQ Left Join with Grouping and Having Convert this TSQL?

Your HAVING clause would filter out any cars which do not match roads. This makes the left join into an inner join You have COUNT(cs. OID) which says it's counting cars, but it doesn't.

You might have meant COUNT(DISTINCT cs. OID) Here's a literal translation: from c in Cars join r in Roads on new {c. OID, c.

RID} equals new {r. OID, r. RID} group new {Car = c, Road = r} by c.

OID into g let carCount = g.Count() //did you mean g. Select(x => x.Car. OID).Distinct().Count() let roadCount = g.

Sum(x => x.Road. Check) where carCount = roadCount select new {OID = g. Key, CarCount = carCount, RoadCount = roadCount} The goal is to return 1 row per OID from the Left Table, where the Count of the records in the left table is equal to the Count of the matching rows in the right table Based on this description, I'd write: var carLookup = Cars.

ToLookup(c => c. OID); var roadLookup = Roads. ToLookup(r => r.

OID); from x in carLookup let carCount = x.Count() let roadCount = roadLookupx.Key.Count() where carCount = roadCount select new {OID = g. Key, CarCount = carCount, RoadCount = roadCount}.

Your HAVING clause would filter out any cars which do not match roads. This makes the left join into an inner join. You have COUNT(cs.

OID) which says it's counting cars, but it doesn't. You might have meant COUNT(DISTINCT cs. OID) Here's a literal translation: from c in Cars join r in Roads on new {c.

OID, c. RID} equals new {r. OID, r.

RID} group new {Car = c, Road = r} by c. OID into g let carCount = g.Count() //did you mean g. Select(x => x.Car.

OID).Distinct().Count() let roadCount = g. Sum(x => x.Road. Check) where carCount = roadCount select new {OID = g.

Key, CarCount = carCount, RoadCount = roadCount} The goal is to return 1 row per OID from the Left Table, where the Count of the records in the left table is equal to the Count of the matching rows in the right table. Based on this description, I'd write: var carLookup = Cars. ToLookup(c => c.

OID); var roadLookup = Roads. ToLookup(r => r. OID); from x in carLookup let carCount = x.Count() let roadCount = roadLookupx.Key.Count() where carCount = roadCount select new {OID = g.

Key, CarCount = carCount, RoadCount = roadCount}.

Nice use of Lookups. I always overlook that method. – Sorax Nov 12 '10 at 20:25 I will give it a try and feedback.

This sample was simplified version of more complex query but has the particular trouble spots. Note cs. OID is unique in Cars, so no need for distinct.

The idea being I have 3 rows in the left table, and I want to return a row only if I also have a matching 3 rows in the right table. Thus the Count(CS. OID) and the SUM(rs.

Check) - in SQL, rs. Check will be null for non matches, and SQL ignores nulls in the sum. – MikeH Nov 12 '10 at 21:20 If you join... cs.

OID will not be unique in the result. And since you're ignoring the nulls, you don't need LEFT JOIN to make nulls for you to ignore. – David B Nov 12 '10 at 21:30 Hmm, with the first example I am getting an error 'WindowsFormsApplication1.

Road' does not contain a definition for 'Check' and no extension method 'Check' accepting a first argument of type 'WindowsFormsApplication1. Road' could be found (are you missing a using directive or an assembly reference? With a blue sqwigle under the word Check in this: Line : let roadCount = g.

Sum(x => x.Road. Check). I'll do some more playing tomorrow.

Thank you all. – MikeH Nov 12 '10 at 22:06 Getting close, and I will play with your examples perhaps finding a solution on my own. Issues with the examples above.

#1 - using the object set in the original question this returns a single row with OID = 1, CarCount =2, RoadCount = 2. Yes, they both have 2, but CarCount should be 3. Thus the left join.

#2 - Results in a message At least one object must implement IComparable. The left join seems to be the sticking point here. – MikeH Nov 12 '107 at 22:13.

I'm not sure I completely understand what the desired results are but here's my shot at it. The check field isn't really necessary with LINQ as it doesn't serve any purpose other than for counting. I misunderstood the importance of the join and what the HAVING clause did for the query.It's now back to the original TSQL version but with the changed RoadCount calculation.

I believe this is what you were asking for. Var results = from Car in cs join road in rs on new { Car. OID, Car.

RID } equals new { road. OID, road. RID } into Roads group roads by Car.

OID into cars let CarCount = cars.Count() let RoadCount = cars. Sum(roads => roads.Count()) where CarCount == RoadCount select new { OID = cars. Key, CarCount, RoadCount }.

Thanks for the attempt. With your example, I end up with two rows. Each containing.

OID = 1, CarCount = 1, RoadCount = 1. With the data in the sample above, results should be empty as there are 3 cars and 2 roads. But if we didn't rely on the HAVING, if we just return the left join, it would be a single row with OID = 1, CarCount = 3, Roadcount = 2.

If I could achieve that, it would be a positive move forward. – MikeH Nov 13 '10 at 21:52 @Mike: Right, it would yield two rows since we joined by matching both OID and RID. Since the result only includes the OID, they look identical since we left out the RID.

But that was based on my interpretation of your TSQL code. Now that I have a better idea of the desired results, it appears we should be grouping and joining just on OID. – Jeff Mercado Nov 14 '10 at 0:44 We can't just group on OID.

We need to group on RID and OID, as that is what makes a match. The catch is that we need to find the grouping of RID + OID where there are matches for all rows in cars. For example.

If I am building an assembly. The assembly will have 3 components. To know if the built assembly is correct, we must compare the build to the rules.

One way to do this, and the SQL shows this) is to left join the build into the rules. If the rules are made up of 3 components, but the build was only made up from 2 components it is a failed build. (in this case a null result) – MikeH Nov 14 '10 at 16:55 I should note that the previous version I had was equivalent to David's Lookup version (which turns out to be what you ended up using).

Neither joined nor grouped by both RID and OID so I'm at a loss for what you are trying to accomplish. Although now, my latest revision makes it equivalent to his literal translation. – Jeff Mercado Nov 14 '10 at 21:11 Looks good.

Thank you. I'm a bit at a loss as well. This was really just a first part.

There is more that I need to do. The goal of this is to replicate the behavior of the TSQL in the post. I've expanded this question into further detail here:stackoverflow.Com/questions/4179550/… – MikeH Nov 14 '10 at 23:10.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.