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, 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

Answer 1

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/

Answer 2

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

Answer 3

User1120430333 posted

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