How to execute nested query in Linq

Question

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.

What is a Vehicle? Why has it got cars etc.? It seems that there should be something like inheritance here. — Gert Arnold
– Gert Arnold, Commented Oct 22, 2012 at 13:39
@ gert, I have given u a simple example.. i wanted to know how to use nested query in accessing the contents... which are present in inner tables — Naruto
– Naruto, Commented Oct 22, 2012 at 14:01
If this is just an example, I suggest replacing Vehicle with Fleet. Then you have a fleet which consists of buses and cars, which makes much more sense. — Bobson
– Bobson, Commented Oct 22, 2012 at 14:15
@Bobson, that's true. but. please help me with answer, if u understood — Naruto
– Naruto, Commented Oct 22, 2012 at 14:17

Silvermind · Accepted Answer · 2012-10-22 21:12:13Z

2

** 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();

edited Oct 22, 2012 at 21:12

answered Oct 22, 2012 at 14:19

Silvermind

5,9943 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Naruto Over a year ago

i have some 7 tables inside one particular class, right now they are just copying the table contents based on only one condition that is ID, now i want to copy the contents based on morethen one condition, i.e first ID of parent class and type present in child class. So urs will work?. i am new, i need to try :(

Naruto Over a year ago

if i dont write first() at the end i can't copy to member variable, i will get type cast error, why is it so?.. in your case i need to manually copy in a loop to Vehicle object.

Naruto Over a year ago

i got it, suppose lets take a example if i try to copy the output directly to my member instead of var, without calling first() or firstOrdefault().. it gives ienumarable linq type cast error, why is it so...

Silvermind Over a year ago

@LLL If you keep it a var and hover over var with your mouse, what does intellisense say the type is?

Silvermind Over a year ago

@LLL I did not read your comment right. If you do not apply FirstOrDefault it will be an IEnumerable. A collection of all hits you can per example do a for each iteration over.

|

Gert Arnold · Accepted Answer · 2012-10-22 14:38:28Z

1

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.

edited Oct 22, 2012 at 14:38

answered Oct 22, 2012 at 14:17

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

9 Comments

Naruto Over a year ago

@Gret Arnold, first() method will copy only top result right?. But inner query will get me all the cars and bus based on type right?..

Naruto Over a year ago

My vehicle ID is unique, but i have nested tables which has multiple petrol and diesel vehicles right?.. So my output will a row of vehicle ID which intern links to multiple petrol vehicles correct?. I'm very new so m asking pls dnt mind

Naruto Over a year ago

Yes somewhat like this only, but why are u using Id = v.vehicleID again in nested query,

Gert Arnold Over a year ago

Nested query? It's an alternative query.

Naruto Over a year ago

i'm sorry, by the way why did you add Id = v.vehicleID inside select, outside you are already comparing right, just for understanding purpose i'm asking

|

Collectives™ on Stack Overflow

How to execute nested query in Linq

2 Answers 2

17 Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related