1

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?

Community
  • 1
  • 1
Sha
  • 2,185
  • 1
  • 36
  • 61

2 Answers2

1

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}
Rahul Singh
  • 21,585
  • 6
  • 41
  • 56
Josh Pendergrass
  • 160
  • 9
0

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.

Nate
  • 761
  • 1
  • 8
  • 27
  • Thanks for answering. I modified my CarTag class to this and added Constructor like this: `public CarTag(int tagid, int carid, Tag tag, Car car) { this.TagId = tagid; this.CarId = carid; this.Tags = tag; this.Cars = car; }` but gives me an error in the loop - it says `"Tag" does not conain a public definition for GetEnumerator`. – Sha Nov 22 '15 at 03:52
  • I suspect that I need to change return new PagedList as well, but to what then? – Sha Nov 22 '15 at 03:55
  • Your CarTag will need a parameterless constructor to work with entity framework. You would be returning PageList. – Nate Nov 22 '15 at 03:57
  • Thank you - I will go ahead and try this. – Sha Nov 22 '15 at 04:24