linq query one to one

Question

trying to get the concert name, date and venue (which is stored on another table) but it doesn't seem to be working, but it keeps pulling back null.

var test = from f in db.Concert
           where f.Name.StartsWith(concertName)
           select new
           {
               f.Name,
               f.StartDateTime,
               venues = from v in db.Venues
                        where v.ID == f.VenueID
                        select v.Address
           }

Edit:

The relationship between Venue and Concert is that Concert has a VenueID that relates to the Venue ID. I need to pass a string back. something like

foreach (var e in test)
{
   html += "<div> e.Name +":" + e.Address </div>;
}

Answer 1

You can use group join to get all Venues related to Concert

var test = from f in db.Concert
           join v in db.Venues on f.VenueID equals v.ID into g
           where f.Name.StartsWith(concertName)
           select new
           {
               f.Name,
               f.StartDateTime,
               Venues = g.Select(x => x.Address)
           };

Usage of results:

foreach (var e in test)
{
   // e.Name
   // e.StartDateTime

   foreach(var address in e.Venues)
      // address
}

Answer 2

It looks like it's safe to assume a 1:1 relationship between Concerts and Venues. Given that, you can use join instead.

var test = from f in db.Concert
           join v in db.Venues on v.ID equals f.VenueID
           where f.Name.StartsWith(concertName)
           select new
           {
               f.Name,
               f.StartDateTime,
               v.Address
           };