Answered by:
How to filter data from two tables

Question
-
User-1526035670 posted
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, VehicleTypeIdIm 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
Tuesday, June 23, 2020 2:32 PM
Answers
-
User1120430333 posted
You would need to use a Linq projection with a join using a custom type, a class, projecting the object
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 24, 2020 1:12 PM
All replies
-
User1120430333 posted
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/
Tuesday, June 23, 2020 3:06 PM -
User-1526035670 posted
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
Tuesday, June 23, 2020 3:25 PM -
User1120430333 posted
You would need to use a Linq projection with a join using a custom type, a class, projecting the object
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, June 24, 2020 1:12 PM