CODEWITHSUNDEEP
c#entity-frameworklinq
Sha
Sha

Reputation: 2407

How to LINQ three tables with query and return all data?

I was looking at this but my question is a bit different. I am building a simplified search engine that allows users to find cars based on tags. I am using WebApi which return JSON data but I can only figure out how to return some of the data I want. In the search engine I want to include a list of all filtered cars but also ALL their associated tags. Current code returns only cars but not tags. Hoping for some help.

Wanted output:

Audi
fast, groovy, slick

BMW
fast, classic

...

I have the following tables from SQL Server and C# strongly typed classes (Entity Framework) that looks something like this:

//    Cars { Id, Name }
//    Tags { Id, Title }
//    CarTags { TagId, CarId }

Cars[] Cars = new Cars[]
{
    new Cars(1, "Audi"),
    new Cars(2, "BMW"),
    new Cars(3, "Chrysler"),
    new Cars(4, "Ford"),
};

Tags[] Tags = new Tags[]
{
    new Tags(1, "fast"),
    new Tags(2, "groovy"),
    new Tags(3, "slick"),
    new Tags(4, "classic"),
};

CarTags[] CarTags = new CarTags[]
{
    new CarTags(1,1),
    new CarTags(2,1),
    new CarTags(3,1),
    new CarTags(1,2),
    new CarTags(4,2)
};

A SQL query could could look like this:

SELECT * FROM Cars c
INNER JOIN
    CarTags ct on c.Id = ct.CarId
INNER JOIN 
    Tags t on ct.TagId = t.Id
WHERE 
    t.Title = 'fast'

... which of course would return all cars associated with the tag "fast".

For LINQ I am doing something like this: 

var q = from c in Cars
        join ct in CarTags on c.Id equals ct.CarId
        join t in Tags on ct.TagId equals t.Id
        where t.Title == "fast"
        select c;

// return data from WebApi 
var page = curPage; // curPage fetched via parameter
var take = 6;
var skip = (page - 1) * take;
return new PagedList<Cars>
{
    Filtered = q.Count(),
    Model = q.Skip(skip).Take(take).ToList()
};

PagedList is like this:

public class PagedList<T>
{
    public int Filtered { get; set; }
    public IEnumerable<T> Model { get; set; }
}

When I loop over this data on the receiving end I use something like this, but I can only enumerate Cars and not Tags.

foreach (var item in q) // item = car object
{
    Console.WriteLine("\n\n" + car.Name);
    //foreach (var tag in item) // can't enumerate all associated tags here
    //{
    //    Console.Write(tag.Title + ", ");
    //}
}

I am stuck at the Linq. How do I accomplish this kind of functionality in Linq?

Upvotes: 1

Views: 396

Answers (2)

Josh Pendergrass
Josh Pendergrass

Reputation: 160

It's your select c line you are telling linq to only return the cars class. To get all of them you could create a new object eg CarsAndTags with properties from both. Then you will update your select statement to be like this.

select new CarsAndTags{Name= c.name,tag=ct.tag}

Upvotes: 1

Nate
Nate

Reputation: 847

In your CarTag class, you can create two new properties. One for car, and one for Tag. These will be foreign keys to the other tables and be of that object. For example, your class would look something like this.

public class CarTag
{
    public int CarId {get;set;}
    public int TagId {get;set;}

    [ForeignKey("CarId")]
    public virtual Car Cars {get;set;}

    [ForeignKey("TagId")]
    public virtual Tag Tags {get;set;}
}

Then your query would be something like this.

var q = from c in Cars
    join ct in CarTags on c.Id equals ct.CarId
    join t in Tags on ct.TagId equals t.Id
    where t.Title == "fast"
    select ct;

This would lazy load both the Car and the Tag for you since they have foreign references.

Upvotes: 0

Related Questions