How to write Dynamic LINQ queries using DBContext in C#

Question

I am modifying my project to use DBContext instead of ObjectContext. My existing code

var query = context.Vehicles.OrderBy("it.VehicleType.VehicleTypeID").
            GroupBy("it.VehicleType.VehicleTypeID", "Min(it.ODO_Reading) AS MinRunVehicle, it.VehicleType.VehicleTypeID");

The above code is written using ObjectContext. After changing my project to inherit from DBContext I am getting the below error

    Error   89  The type arguments for method 'System.Linq.Queryable.OrderBy<TSource,TKey>(System.Linq.IQueryable<TSource>, System.Linq.Expressions.Expression<System.Func<TSource,TKey>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

I want to know how to specify dynamic Linq query in DBContext. Can somebody help.

You don't need dynamic queries, you can create a query using a fluent style by successively applying LINQ functions, eg `q= q.Where(...); if (x){q=q.GroupBy(...).OrderBy(...);}` — Panagiotis Kanavos, Nov 04 '15 at 10:49
Yes I can write queries using LINQ methods but I should not use LINQ methods as my project insists to use dynamic queries. — LalithaT, Nov 06 '15 at 09:53
There's no such thing as dynamic LINQ queries. Are you confusing LINQ with [eSQL](https://msdn.microsoft.com/en-us/library/vstudio/bb399560(v=vs.100).aspx)? Your first example is *not LINQ*, it's eSQL — Panagiotis Kanavos, Nov 06 '15 at 09:56
I want to write the above ESQL using [DBContext]. Earlier my class inherited from ObjectContext. ESQL supported in ObjectContext but i am not able to use DBContext for writing ESQL. — LalithaT, Nov 06 '15 at 11:01
DbContext contains an ObjectContext. You can get to it with `((IObjectContextAdapter)context).ObjectContext` ... — Panagiotis Kanavos, Nov 06 '15 at 11:13
Yes, but I cannot get the entities using the object created using above syntax. For e.g., `objectContext = (this as IObjectContextAdapter).ObjectContext; var customers = context.objectContext.Vehicles.... is not possible.`.I am not getting the **Vehicles** entity using above casting — LalithaT, Nov 06 '15 at 11:25

score 0 · Answer 1 · answered Nov 04 '15 at 10:28

0

You can use

var query = context.Vehicles.OrderBy(m=>m.it.VehicleType.VehicleTypeID)

answered Nov 04 '15 at 10:28

Jhonie

11
1
4

Aydin · Answer 2 · 2015-11-04T11:34:47.173

Here's how to convert it all including OrderBy and GroupBy

void Main()
{

    var vehicles = new List<Vehicle>();

    for (int i = 0; i < 10; i++)
    {
        vehicles.Add(new Vehicle());
    }

    vehicles.OrderBy(vehicle     => vehicle.VehicleType.VehicleTypeID)
            .GroupBy(vehicleType => vehicleType.VehicleType.VehicleTypeID, 
                     vehicle     => vehicle.ODO_Reading, 
                                    (vehicleTypeID, minRunVehicle) => new 
                                    {
                                        VehicleTypeId = vehicleTypeID, 
                                        minRunVehicle = minRunVehicle
                                    })
            .ToList()
            .ForEach(vehicle => 
                     {
                         Console.WriteLine(vehicle.VehicleTypeId);
                         vehicle.minRunVehicle.ToList()
                                              .ForEach(minRun =>
                                              {
                                                  Console.WriteLine ("\t > :" + minRun);
                                              });
                         Console.WriteLine ("\n");
                     });
}

public class Vehicle
{
    public Vehicle()
    {
        this.VehicleType = new VehicleType();
        this.ODO_Reading = random.Next(100, 100000);
    }

    public VehicleType VehicleType { get; set;  }
    public int ODO_Reading { get; set; }
}

public class VehicleType
{
    public VehicleType()
    {
        VehicleTypeID = random.Next(1, 10);
    }

    public int VehicleTypeID { get; set; }
} 

public static Random random = new Random();

Where are you finding the Min value of ODO_Reading? – LalithaT Nov 06 '15 at 05:38 — LalithaT, Nov 06 '15 at 05:38

score 0 · Accepted Answer · answered Nov 06 '15 at 11:12

0

The first example uses eSQL, not some type of dynamic LINQ.

DbContext doesn't allow you to perform eSQL queries directly, but you can get access to the underlying ObjectContext and use it as before:

var query = ((IObjectContextAdapter)context).ObjectContext
                                            .CreateQuery<Vehicle>("<ESQL Query>")

answered Nov 06 '15 at 11:12

Panagiotis Kanavos

120,703
13
188
236

But if you see my query it includes ESQL in GroupBy and OrderBy..Is it possible to write these in ESQL? – LalithaT Nov 06 '15 at 12:02
I got it. I can give as below `var query1 = context.objectContext.CreateQuery("TIME'23:59:59'").OrderBy(x => x.EmployeeName)` – LalithaT Nov 06 '15 at 12:19

How to write Dynamic LINQ queries using DBContext in C#

3 Answers3