How to filter data from two tables[Answered] RSS

• • Reply
  JamieP1

  Member

  26 Points

  81 Posts

  How to filter data from two tables

  Jun 23, 2020 02:32 PM|JamieP1|LINK

  Hi 

  Im using Linq to SQL. Below is a sample of my tables structure (2 tables):

  Customer: Id, Name
  VehicleType: Id, Name
  Vehicles: Id, CustomerId, VehicleTypeId

  Im using a Linq to Sql query where Im returning a Customer type but also would like to return the Customer type by passing in/filtering by a single vehicle. I tried this but didnt compile

  query = ctx.Customers.Where(c => c.Vehicles.Contains(c.id));

  The error i received is "cannot convert from 'int' to 'Vehicle' "

  I then attempted with

  var veh = ctx.Vehicles.FirstOrDefault(v => v.VehicleTypeId == 1);
  query = ctx.Customers.Where(c => c.Vehicles.Contains(veh));

  Which compiled but doesnt return all the data? How could i return the customer type with all associated vehicles?

  Thanks

• • Reply
  DA924

  Contributor

  4963 Points

  4208 Posts

  Re: How to filter data from two tables

  Jun 23, 2020 03:06 PM|DA924|LINK

  How could i return the customer type with all associated vehicles?

  You need to learn how to use the Include statment for getting data from related entity.

  https://docs.microsoft.com/en-us/ef/ef6/querying/related-data

  You use a Linq Join.

  https://www.tektutorialshub.com/entity-framework/join-query-entity-framework/

  If you find the post has answered your issue, then please mark post as 'answered'.
• • Reply
  JamieP1

  Member

  26 Points

  81 Posts

  Re: How to filter data from two tables

  Jun 23, 2020 03:25 PM|JamieP1|LINK

  Hi

  Sadly this project doesnt have EF but i digged around with the Join statement and might be missing a thing or two. It compiles but i just need to return this as a Customer. As i understand this is an anonymous type (cs below for testing) so i couldnt bind it to query (from above code) as thats of type IQueryable<Customer> so 

  var cs = ctx.Customers.Join(
                      ctx.vehicles, c => c.id, v => v.VehicleId,
                      ((customer, vehicle) => new
                      {
  
                      }));

  this is my current attempt but if i could bind this as IQueryable<Customer> hopefully that should resolve.

  Alternatively i could get all the Vehicles by id and then have a foreach loop to get the Customer and convert it which i feel is a little long winded.

  Thanks again

• • Reply
  DA924

  Contributor

  4963 Points

  4208 Posts

  Re: How to filter data from two tables

  Jun 24, 2020 01:12 PM|DA924|LINK

  You would need to use a Linq projection with a join using a custom type, a class, projecting the object 

  https://csharp-station.com/Tutorial/Linq/Lesson02

  If you find the post has answered your issue, then please mark post as 'answered'.