Have Geek Brain

Linq to sql using

Can anyone show me how to write a query using linq to sql to search for suburbs that have at least 1 location in a particular state

Location

SuburbID
SuburbName
StateName

A place

VenueID
VenueName
SuburbName
StateName
0


a source to share


2 answers 


var locations = from loc 
                in dataContext.Locations 
                where loc.Venues.Count > 1 
                select loc

EDIT: Final answer:

If you have foreign key setup between Location / Venue:

string stateName = "New York";  

var locations = from loc 
                in dataContext.Locations 
                where loc.Venues.Count > 1 && loc.StateName == stateName 
                select loc;


If there is no foreign key relationship:

string stateName = "New York";

var locations = (from v 
                 in dataContext.Venues 
                 where v.StateName == stateName 
                 select (from l 
                         in dataContext.Locations 
                         where l.SuburbName == v.SuburbName && l.StateName == v.Statename              
                         select l
                        ).Single()).Distinct();


True, you have to fix your tables. The "Venue" table should have the suburbID attribute instead of "StateName" and "SuburbName" - it is redundant to save both.

+2


a source


Here are some simple answers:

var suburbNames = dataContext.Venues
  .Where(v => v.StateName == specificState)
  .GroupBy(v => v.SuburbName)
  .Select(g => g.Key)

           //

var locations = dataContext.Location
  .Where(loc => loc.StateName == specificState)
  .Where(loc => loc.Venues.Any())


With this Venues: property, you can get this by adding relations to linq in the sql constructor - even if the foreign key doesn't exist / isn't used in the database.

0


a source






More articles:


All Articles