CODEWITHSUNDEEP
linqjoinlinq-to-sqllinq-group
rescueme
rescueme

Reputation: 69

group by and joining tables in linq to sql

I have the following 3 classes(mapped to sql tables).

Places table:
Name(key)
Address
Capacity

Events table:
Name(key)
Date
Place

Orders table:
Id(key)
EventName
Qty

The Places and Events tables are connected through Places.Name = Events.Place, while the Events and Orders tables: Events.Name = Orders.EventName . The task is that given an event, return the tickets left for that event. Capacity is the number a place can hold and Qty is the number of tickets ordered by someone. So some sort of grouping in the Orders table is needed and then subtract the sum from capacity.

Upvotes: 0

Views: 171

Answers (1)

Yvo
Yvo

Reputation: 19263

Something like this (C# code sample below)?

Sorry for the weird variable names, but event is a keyword :)
I didn't use visual studio, so I hope that the syntax is correct.

string eventName = "Event";

var theEvent = Events.FirstOrDefault(ev => ev.Name == eventName);
int eventOrderNo = Orders.Count(or => or.EventName == eventName);

var thePlace = Places.FirstOrDefault(pl => pl.Name == theEvent.Place);
int ticketsLeft = thePlace.Capacity - eventOrderNo;

If the Event has multiple places, the last two lines would look like this:

int placesCapacity = Places.Where(pl => pl.Name == theEvent.Place)
                           .Sum(pl => pl.Capacity);

int ticketsLeft = placesCapacity - eventOrderNo;

On a sidenote
LINQ 101 is a great way to get familiar with LINQ: http://msdn.microsoft.com/en-us/vcsharp/aa336746

Upvotes: 1

Related Questions