CODEWITHSUNDEEP
c#sqllinqentity-frameworklinq-to-sql
Naruto
Naruto

Reputation: 9634

How to execute nested query in Linq

I have a LINQ class of this format in C#:

class Vehicle
{
  int _VehicleID
  int _ModelID

  EntetySet<Cars> _AllCars
  EntetySet<Bus> _AllBus
  EntetyRef<Driver> _Person
}

Cars table

CarsID | Manufacturer | Type
1000   | Honda        | Diesel
1001   | Mitsubishi   | Petrol
1002   | Maruti       | Diesel

Bus table

BusID | Manufacturer | Type
2000  | Volvo        | Diesel
2001  | TATA         | Petrol
2002  | layland      | Petrol

From the UI, I will get a Vehicle ID as a parameter. Based on that, all the cars, Bus and its associated tables will get copied to a variable. That is:

Vehicle v1 = new Vehicle();
v1 = dc.vehicle.where(v => v.vehicleID == param.vehicleID).findfirst();

My v1 has all the table and its contents which satisfies the above condition.

Now, I want to filter some more based on the table content present in table cars and bus; i.e based on type of vehicle petrol or diesel.

If I want to copy all the petrol cars and Buses using a query statement in a single line, please tell me the way.

Thanks in advance.

Upvotes: 0

Views: 466

Answers (2)

Silvermind
Silvermind

Reputation: 5944

** Edit based upon comments **

The logic of your question somehow slipped by me, because I was taking a logical Human approach on vehicle. Whenever I look at this Vehicle class I instantly see cars and busses as vehicles to. This indicates more that the naming is poorly chosen. But no more judging from me.

I understand you want one vehicle by id and you want the result to only contain cars and busses riding on let's say Petrol, in a single line. If it is translated to SQL than I don't know how far the tree can go within a select, but if it is in memory than you can go a long way inside a subselect.

You could use a 'Model' class to achieve that or you could go with an anonymous type, but I will give an example for a 'Model' class:

Example:

public class VehicleModel
{
    public int VehicleID { get; set; }
    public int ModelID { get; set; }
    public List<Cars> Cars { get; set; }
    public List<Bus> Buses { get; set; }
}

var vehicleID = 1; // just for the example of course.
var fuelType = "Petrol";
var vehicle = (from v in dc.vehicle 
               where v._VehicleID == vehicleID
               select new VehicleModel
               {
                   VehicleID = v._VehicleID,
                   ModelID = v._ModelID,
                   Cars = v._AllCars.Where(car => car.Type == fuelType).ToList(),
                   Buses = v._AllBus.Where(bus => bus.Type == fuelType).ToList()
               }).SingleOrDefault();
               // use FirstOrDefault() when _VehicleID is NOT unique to get TOP 1

Do not modify the EntitySets within the Entity itself, always use a model for these things, because if you would do that and call save changes on your EntityContainer all sorts of things could go wrong.

If you want to know about anonymous types take a look here: http://msdn.microsoft.com/en-us/library/bb397696(v=vs.100).aspx

Also take a look here for Linq examples: http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b




- before edit

Do you mean this:

var query = dc.vehicle.Where(v => 
                             v._AllCars.Any(c => c.Manufacturer == "Honda") ||
                             v._AllBuss.Any(b => b.Manufacturer == "Volvo"));

This will give you all vehicles where the cars are Honda Or the Busses are Volvo. Which will be:

1000 | Honda | Diesel
2000 | Volvo | Diesel

the result is an IEnumerable containing all or no items satisfying the criteria.

If you only want the first hit you can do:

//if it must exist, otherwise this throws an exception
var firstVehicle = query.First();
//if it may exist, otherwise null
var firstVehicle = query.FirstOrDefault();

Upvotes: 2

Gert Arnold
Gert Arnold

Reputation: 109080

It seems that you want something like this:

var v1 = dc.vehicle.Where(v => v.vehicleID == param.vehicleID
                            && v.Cars.All(c => c.Type == "Petrol")
                            && v.Buses.All(c => c.Type == "Petrol")).First();

)

to get a "Vehicle" of which all cars and buses have petrol. (Note that I use "Cars" and not the unconventional name "_AllCars", etc.).

Edit

Or:

var v1 = dc.vehicle.Where(v => v.vehicleID == param.vehicleID)
    .Select(v => new {
                        Id = v.vehicleID,
                        Cars = v.Cars.Where(c => c.Type == "Petrol"),
                        Buses = v.Buses.Where(c => c.Type == "Petrol")
                     }).First();

to get an anonymous type with filtered collections.

Upvotes: 1

Related Questions