How to select multiple tables using LINQ

Question

Please forgive me if I'm not providing the right information, I'm a newbie with this MVC thing.

I am using the LINQ statement below, the problem is the Image URL is not displaying. Any ideas why? Thanks in advance

Controller:

public ActionResult Index()
{
    var cars = from c in db.Cars
               from m in db.Makes from u in db.CarImages 
               where c.carID == m.makeID && u.CarID == c.carID && (u.thumbnail.Equals("true")) 
               select c;
    return View(cars.ToList());
}

View:

@Html.DisplayFor(modelItem => item.CarImage.imageUrl)

A bit more info: I used the code first approach.

Car Model:

public class Car
    {
        public int carID { get; set; }
        public int makeID {get; set;}
        public string registration { get; set; }
        public string color { get; set; }
        public Decimal price { get; set; }
        public string fuel { get; set; }
        public DateTime tax { get; set; }
        public DateTime mot { get; set; }
        public DateTime manufactureDate { get; set; }
        public string shortDescription { get; set; }
        public string longDescription { get; set; }

        public virtual Make Make { get; set; }
        public virtual CarModel CarModel { get; set; }
        public virtual CarImage CarImage { get; set; }

    }

CarImage Model

public class CarImage
    {
        public int CarImageID { get; set; }
        public int CarID { get; set; }
        public string imageUrl { get; set; }
        public string thumbnail { get; set; }
    }

Make Model

public class Make
    {
        public int makeID { get; set; }
        public string makeName { get; set; }
    }

DBContext

public class CarStudioDBContext : DbContext
    {

            public DbSet<Car> Cars { get; set; }
            public DbSet<Make> Makes { get; set; }
            public DbSet<CarImage> CarImages { get; set; }
            public DbSet<CarModel> CarModels { get; set; }

    }

Thanks for the help guys

As i revisit this question, do you have foreign keys setup in your database between cars, makes and images? — Tommy
– Tommy, Commented Feb 10, 2014 at 15:45
So everything is getting display just other than image? Have you checked if the image path is right in F12 Developer tools? Or are you getting any specific exceptions, if so at which line of code? — ramiramilu
– ramiramilu, Commented Feb 10, 2014 at 17:07
Thanks ramiramilu, I was checking the value returned from the database rather than if the path was correct. No need to worry, I'm taking a different approach now — magekingQue
– magekingQue, Commented Feb 10, 2014 at 17:31

Tommy · Accepted Answer · 2014-02-10 16:04:37Z

1

Instead of using the Html.DisplayFor() helper, simply output the value of the property

@item.CarImage.imageUrl

Or, if you want it to be an actual image, you could do something like the following

<img src="@item.CarImage.imageUrl" alt="Car Pic"/>

Additional info:

I think you are missing the tie back from CarImages to Cars. I see you have your foreign key (carId), but you also need to have an entity object tying it back to a car.

public class CarImage
    {
        public int CarImageID { get; set; }
        public int CarID { get; set; }
        public string imageUrl { get; set; }
        public string thumbnail { get; set; }

        public virtual Car Car { get; set; }
    }

Based on the examples here. You would need to do the same for Make, but in a different way. Lastly, I don't think you would need a complex of a linq query once everything is tied together.

edited Feb 10, 2014 at 16:04

answered Feb 10, 2014 at 15:36

Tommy

39.9k10 gold badges93 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

magekingQue Over a year ago

Hi Tommy, thanks for the help. I have added my models for a bit more insight. I tried using the method you suggested but I got a null pointer exception.

magekingQue Over a year ago

Thanks Tom, decided to use store images in the same table for now. Guess I have to crawl before I run.

Eric.Y.Fan · Accepted Answer · 2014-02-10 18:09:59Z

1

You might want to check if the LINQ return correct data, as I can see you are using carID to join with makeID, that doesn't look correct.

Actually you don't need to join the tables in linq, just need to use the Include method to include the properties:

var cars = from c in db.Cars.Include("Make").Include("CarImage")
           select c;

edited Feb 10, 2014 at 18:09

answered Feb 10, 2014 at 15:44

Eric.Y.Fan

2361 silver badge5 bronze badges

2 Comments

magekingQue Over a year ago

Hi Eric. I think I set my foreign keys right. Please have a look at my update

Eric.Y.Fan Over a year ago

Still I don't understand why you use carID==makeID in Linq. Furthermore, as the CarImage is from another table, it won't be included in the result automatically, you need to explicitly select the property in your select clause

Collectives™ on Stack Overflow

How to select multiple tables using LINQ

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related