LINQ multi-table query - only 2 of 3 tables are returned

Question

QUERY: I want to return all restaurants with a CUISINE__BANE = cusisineName regardless of the city in which they are located. My query returns the RESTAURANT and CITY tables but not the CUISINE table. Your help is greatly appreciated!

public class DLgetRestaurants
    {
        DL.FVRGDataContext db = new FVRGDataContext();

        public List<RESTAURANT> getRestaurants(string cuisineName)
        {
            var cuisineID = db.CUISINEs.First(s => s.CUISINE_NAME == cuisineName).CUISINE_ID;

            List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs
                                       join CITY in db.CITies on RESTAURANT.CITY_ID equals CITY.CITY_ID
                                       join CUISINE in db.CUISINEs on RESTAURANT.CUISINE_ID equals CUISINE.CUISINE_ID
                                       where RESTAURANT.CUISINE_ID == cuisineID
                                       select RESTAURANT).ToList();

             return result;
        }
    }

Accept some of your other question's answers before asking new questions — Aducci, Jul 11 '11 at 18:39
sorry, I thought I had accepted everything. I will check again. — SUSAN, Jul 11 '11 at 18:45
You need to click on the Check Mark beside the question that helps you solve your problem — Aducci, Jul 11 '11 at 18:48
Don't quite understand the question, you currently return a list of restaurants, can you explain *"My query returns the RESTAURANT and CITY tables but not the CUISINE table."* — BrokenGlass, Jul 11 '11 at 18:50
This is a restaurant review site. The user selects a cuisine and all the restaurants having that cuisine are selected. The query should return the cusine name, the restaurant name and the city name. I have used the O/R designer to create the entity classes for all the tables in my design. Does that help??? ~susan~ — SUSAN, Jul 11 '11 at 18:56
Thanks Aducci. I have been leaving all these comments saying thanks and thought that was enuf. My aplogies for any lack of courtesy (my bad!!! — SUSAN, Jul 11 '11 at 18:57

BrokenGlass · Accepted Answer · 2011-07-11T20:08:37.660

I doubt that your query actually does return the City - you currently only select the restaurant in your query, instead you could project to a special type that has the info you want:

class RestaurantByCuisine
{
  public string RestaurantName {get;set;}
  public string CityName {get;set;}
  public string CuisineName {get;set;}
}

public List<RestaurantByCuisine> getRestaurants(string cuisineName)
{
    var cuisineID = db.CUISINEs.First(s => s.CUISINE_NAME == cuisineName).CUISINE_ID;

    List<RestaurantByCuisine> result = (from RESTAURANT in db.RESTAURANTs
                                join CITY in db.CITies on RESTAURANT.CITY_ID equals CITY.CITY_ID
                                join CUISINE in db.CUISINEs on RESTAURANT.CUISINE_ID equals CUISINE.CUISINE_ID
                                where RESTAURANT.CUISINE_ID == cuisineID
                                select new RestaurantByCuisine() 
                                { 
                                    RestaurantName = RESTAURANT.Name,
                                    CityName = CITY.Name,
                                    CuisineName = CUISINE.Name
                              ).ToList();

    return result;
}

This assumes you entities have a Name property, substitute with the right property name otherwise.

Note that your Restaurant entity already has CITY_ID, CUISINE_ID properties, so if this is a foreign key to the respective tables you also should have CITY and CUISINE properties. These will be populated if you use an Include() query on these properties, the query would then look like this:

List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs.Include(x => x.CITY).Include(x => x.CUISINE)
                            where RESTAURANT.CUISINE_ID == cuisineID
                            select RESTAURANT
                            ).ToList();

Edit:

It sounds like you are using Linq to Sql, in this case you can specify the load options directly on your data context:

DL.FVRGDataContext db = new FVRGDataContext();
var dlo = new DataLoadOptions();
dlo.LoadWith<RESTAURANT>(r => r.CITY);
dlo.LoadWith<RESTAURANT>(r => r.CUISINE);
db.LoadOptions = dlo;

Then the following should work:

List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs
                           where RESTAURANT.CUISINE_ID == cuisineID
                           select RESTAURANT
                           ).ToList();

I am receiving the following error on 'RestaurantByCuisine': The type or namespace could not be found. — SUSAN, Jul 11 '11 at 19:38
@Susan: It sounds like you are using Linq to Sql, try out my last suggestion. — BrokenGlass, Jul 11 '11 at 20:09
Do I need a using statement or assembly reference to use DataLoadOptions ... I'm getting the red line from Intellisense. — SUSAN, Jul 11 '11 at 23:03
Added: using System.Data.Linq; Now I'm getting an error on the db.LoadOptions statement: Setting load options is not allowed after results have been returned from a query. — SUSAN, Jul 11 '11 at 23:24

score 0 · Answer 2 · answered Jul 11 '11 at 19:20

0

You can also re-do your select like this:

        List<RESTAURANT> result = (from RESTAURANT in db.RESTAURANTs.Include("CITY").Include("CUISINE")
                                   where RESTAURANT.CUISINE_ID == cuisineID
                                   select RESTAURANT).ToList();

answered Jul 11 '11 at 19:20

TheGeekYouNeed

7,509
2
26
43

Oh boy! I tried for a week to figure out the INCLUDE but Intellisense did not recognize the syntax. I'm wondering if it has something to do with the creation of the entity framework classes??? I created the entity classes by dragging the tables to the O/R designer. I can see the table relationships in the view. Any suggestions??? – SUSAN Jul 11 '11 at 19:27
Is what I gave you working? I'm not sure what you mean about not getting the Include on intelisense. Intelisense won't tell you what tables you can include, that's up to you. – TheGeekYouNeed Jul 11 '11 at 19:41
Error 2 'System.Data.Linq.Table' does not contain a definition for 'Include' and no extension method 'Include' accepting a first argument of type 'System.Data.Linq.Table' could be found (are you missing a using directive or an assembly reference?) H:\ASP.Net\FVRG\DL\DLgetRestaurants.cs 28 74 DL – SUSAN Jul 11 '11 at 19:46
These are the steps I took: (1) created my tables with relationships, (2) created LINQ to SQL dbml, (3) dragged my tables into the Object Relationsal Designer. I thought that I am using entity classes and hence entity framework ... is that correct. ~susan~ – SUSAN Jul 11 '11 at 19:51
1

No, Entity Framework uses an ADO Entity Data Model (Edmx). Here is a good link with an answer on the differences. http://stackoverflow.com/questions/8676/entity-framework-vs-linq-to-sql – TheGeekYouNeed Jul 11 '11 at 20:20

score 0 · Answer 3 · answered Jul 11 '11 at 21:10

What is it you are trying to achieve here? If you simply need to access the Cuisine from each Restuarant you will be able to iterate through the List<Restuarant> like so...

List<Restuarant> restuarants = GetList(); // Get the list however you like.
foreach(var r in restuarants)
{
     // Now you can access Cuisine
     var cuisine = r.Cuisine;
}

You can also get a list of Cuisine like this...

var cuisines = restuarants.Where(c => c.CuisineName == "chips").ToList();

Or something similar.

LINQ multi-table query - only 2 of 3 tables are returned

3 Answers3