Help with Linq to SQL; return one result from one to many relationship

Question

I am trying to return a list of results, where the joined table has multiple values, but I only want one image value for each listing value.

The SQL is like this:

SELECT
    Title, Comments, ThumbNailPath, thumbheight, thumbwidth
FROM  
      Listings as l INNER JOIN
      Images as i ON l.id = i.ListingId
WHERE
    (i.ImageSlot = 1)

My repository class looks something like this: (db.GetListings() is the stored procedure method I added to the methods section of the .dbml file)

private ListingModelDataContext  db = new ListingModelDataContext();

public IQueryable FindAllListings()
    {
       //return  all listings and first associated thumbnail
        return db.GetListings();                   
    }

When I try calling from stored procedure I get error

'System.Data.Linq.ISingleResult' to 'System.Linq.IQueryable'. An explicit conversion exists (are you missing a cast?)

I am looking for guidance on how I might either call that sql statement from a stored procedure or simply structure the linq to return that value.

My preference is to know how to write the LINQ statement. To clarify, I have a one to many relationship on the images table. (multiple images per listing) I only want to return the first image though on a search results page. So, for each listing return image value where imageSlot = 1.

I should get a list of rows showing the listing values joined to the image values. I get the correct results in SQL but not sure how to write it in linq or how to call the sproc correctly.

Thanks in advance and sorry if this is hard to read.

Answer 1

Are you trying to do soemthing like :

 var result = (stored proc).Single()

or

 var result = (stored proc).SingleOrDefault()

?? This would break if there are indeed multiple elements coming back - instead use the .First() or .FirstOrDefault() methods:

var result = (stored proc).FirstOfDefault();

returns the first entry from the stored proc, or null if the stored proc returns no values at all.

Marc