generate EF orderby Expression by string




I want to generate expression by string parameter,some code like:



```
 private Expression<Func<Task, T>> Generate(string orderby)
    {
        switch (orderby)
        {
            case "Time":  
                return t => t.Time;
            case "Money":
                return t => t.RewardMoney;
            default:
                return t => t.Id;
        }
    }

```

then call it:



```
_context.Items.OrderBy(Generate("Money"));

```

But It can't compile! I change T to object.



```
private Expression<Func<Task, object>> Generate(string orderby)

```

Then It can compile, but It doesn't work.



> 
> System.NotSupportedException: Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types.
> 
> 
>
Using [reflection](/questions/tagged/reflection "show questions tagged 'reflection'") and [expression-trees](/questions/tagged/expression-trees "show questions tagged 'expression-trees'") you can provide the parameters and then call `OrderBy` function, Instead of returning `Expression<Func<Task, T>>` and then calling `OrderBy`.


Note that `OrderBy` is an extension method and has implemented in both `System.Linq.Enumarable` and `System.Linq.Queryable` classes. The first one is for [linq-to-objects](/questions/tagged/linq-to-objects "show questions tagged 'linq-to-objects'") and the latter is for [linq-to-entities](/questions/tagged/linq-to-entities "show questions tagged 'linq-to-entities'"). [entity-framework](/questions/tagged/entity-framework "show questions tagged 'entity-framework'") needs the expression tree of the query in order to translate it to SQL commands. So we use the `Queryable` implementation.


It can be done by an extension method(explanations added as comments):



```
public static IOrderedQueryable<TSource> OrderBy<TSource>(
       this IQueryable<TSource> query, string propertyName)
{
    var entityType = typeof(TSource);

    //Create x=>x.PropName
    var propertyInfo = entityType.GetProperty(propertyName);
    ParameterExpression arg = Expression.Parameter(entityType, "x");
    MemberExpression property = Expression.Property(arg, propertyName);
    var selector = Expression.Lambda(property, new ParameterExpression[] { arg });

    //Get System.Linq.Queryable.OrderBy() method.
    var enumarableType = typeof(System.Linq.Queryable);
    var method = enumarableType.GetMethods()
         .Where(m => m.Name == "OrderBy" && m.IsGenericMethodDefinition)
         .Where(m =>
         {
            var parameters = m.GetParameters().ToList();
            //Put more restriction here to ensure selecting the right overload                
            return parameters.Count == 2;//overload that has 2 parameters
         }).Single();
    //The linq's OrderBy<TSource, TKey> has two generic types, which provided here
    MethodInfo genericMethod = method
         .MakeGenericMethod(entityType, propertyInfo.PropertyType);

    /*Call query.OrderBy(selector), with query and selector: x=> x.PropName
      Note that we pass the selector as Expression to the method and we don't compile it.
      By doing so EF can extract "order by" columns and generate SQL for it.*/
    var newQuery = (IOrderedQueryable<TSource>)genericMethod
         .Invoke(genericMethod, new object[] { query, selector });
    return newQuery;
}

```

Now you can call this overload of `OrderBy` like any other overload of it.  

For example:



```
var cheapestItems = _context.Items.OrderBy("Money").Take(10).ToList();

```

Which translates to:



```
SELECT TOP (10)  {coulmn names} FROM  [dbo].[Items] AS [Extent1] 
       ORDER BY [Extent1].[Money] ASC

```

This approach can be used to define all overloads of `OrderBy` and `OrderByDescending` methods to have `string` property selector.
You are mixing [expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expression?view=netframework-4.7.1) with [lambda expressions](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression?view=netframework-4.7.1). There are many posts showing how you can combine lambda expressions, but the essential part is to compose expressions from lambda expression [bodies](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.body?view=netframework-4.7.1) and rebind the [parameters](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.lambdaexpression.parameters?view=netframework-4.7.1).


The later is usually achieved by a custom [`ExpressionVisitor`](https://docs.microsoft.com/en-us/dotnet/api/system.linq.expressions.expressionvisitor?view=netframework-4.7.1) like this:



```
using System.Linq.Expressions;

public static class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

```

Now regarding the EF Core combined query filters. 


Using dictionaries and expression lists seems overcomplicated for what are you doing. Since [`IMutableEntityType`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype?view=efcore-2.1) provides read/write access to the [`QueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.imutableentitytype.queryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_IMutableEntityType_QueryFilter), the same can be achieved with a small set of a custom extension methods.


All they go inside a class like this:



```
using System;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public static class QueryFilterExtensions
{
}

```

First method:



```
public static void AddQueryFilter(this IMutableEntityType target, LambdaExpression filter)
{
    if (target.QueryFilter == null)
        target.QueryFilter = filter;
    else
    {
        var parameter = target.QueryFilter.Parameters[0];
        var left = target.QueryFilter.Body;
        var right = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        var body = Expression.AndAlso(left, right);
        target.QueryFilter = Expression.Lambda(body, parameter);
    }
}

```

This is a non generic method which combines the exiting filter with passed filter using `AndAlso` (C# `&&`) operator and shows the aforementioned lambda expression combining principles.


However it's not so useful directly, like inside your entity type configuration loop (it can, but requires you to build manually the lambda expression instead of letting C# compiler do that). So here comes the second method:



```
public static void AddQueryFilter<T>(this IMutableEntityType target, Expression<Func<T, bool>> filter)
{
    LambdaExpression targetFilter = filter;
    if (target.ClrType != typeof(T))
    {
        var parameter = Expression.Parameter(target.ClrType, "e");
        var body = filter.Body.ReplaceParameter(filter.Parameters[0], parameter);
        targetFilter = Expression.Lambda(body, parameter);
    }
    target.AddQueryFilter(targetFilter);
}

```

It's a generic method - not quite type-safe, but allows you to use a compile time lambda expression and bind it to the actual entity type as follows:



```
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
    if (typeof(IDeleted).IsAssignableFrom(entityType.ClrType))
        entityType.AddQueryFilter<IDeleted>(e => e.DateTimeDeleted == null);
}

```

Looks better, isn't it :)


The last custom extension method is complement to (replacement of) the standard EF Core generic [`HasQueryFilter`](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.metadata.builders.entitytypebuilder-1.hasqueryfilter?view=efcore-2.1#Microsoft_EntityFrameworkCore_Metadata_Builders_EntityTypeBuilder_1_HasQueryFilter_System_Linq_Expressions_Expression_System_Func__0_System_Boolean___) method:



```
public static EntityTypeBuilder<TEntity> AddQueryFilter<TEntity>(this EntityTypeBuilder<TEntity> target, Expression<Func<TEntity, bool>> filter)
    where TEntity : class
{
    target.Metadata.AddQueryFilter(filter);
    return target;
}

```

and allows you to replace



```
this.AddToDict<DbMyEntity>(t => t.Name == null || t.Name == "Something");

```

with the more convenient



```
modelBuilder.Entity<DbMyEntity>()
    .AddQueryFilter(t => t.Name == null || t.Name == "Something");

```

**Update (EF Core 3.0):** `QueryFilter` property has been replaced with `GetQueryFilter` and `SetQueryFilter` extension methods. Not sure about `ThenInclude()` and EF 7, but you can do something like this in your repositories (tested with EF 6):



```
public MyEntity GetMyEntity_EagerlyLoad(DbContext context, int id, params Expression<Func<MyEntity, object>>[] propertiesToLoad)
{
    var q = context.MyEntities.Where(m => m.ID == id);

    foreach (var prop in propertiesToLoad)
        q = q.Include(prop);

    return q.SingleOrDefault();
}

```

You can then call it like this:



```
repo.GetMyEntity_EagerlyLoad(context, id, m => m.Property1, m => m.Property2, m => m.Property1.NestedProperty)

```

  



**EDIT**: There is also the alternative way to do eager loading with EF using projection. You could do a generic repository method like this:



```
public MyEntity GetMyEntity_EagerlyLoad<T>(DbContext context, int id, Expression<Func<MyEntity, T>> loadingProjection, Func<T, MyEntity> resultProjection)
{
    var q = context.MyEntities.Where(m => m.ID == id);

    return q.Select(loadingProjection).AsEnumerable().Select(resultProjection).SingleOrDefault();
}

```

And then call it with the properties you want loaded and the entity you want the method to return:



```
repo.GetMyEntity_EagerlyLoad(context, id, m => new { myEntity = m, m.Property1, m.Property2, m.Property1.NestedProperty }, m => m.myEntity)

``` A few things to try.


1. Use [Variable Tables](http://odetocode.com/Articles/365.aspx) instead -> maybe the import wizard prefers that?
2. Name your return fields.


Try using the following stored proc (untested .. just thinking out loud...)



```
CREATE PROCEDURE dbo.Foo
AS

    SET NOCOUNT ON

    DECLARE @ResultTable TABLE (SomeId INTEGER)

    INSERT INTO @ResultTable
    SELECT DISTINCT Id AS Identity -- Or u can rename this field to anything...
    FROM SomeExistingTableWhichHasAnIdentityField

GO

```

Try that and see if the wizard refreshes, now.


-- 


 Attempt #2 :)


Ok .. when the EF designer/wizard/whatever fails to figure out EXACTLY what my stored proc is suppose to be returning, I usually do the following :-


1. Make sure the stored procedure doesn't exist at all in the EF designer/context, etc. (You have a clean starting point)
2. Open up your stored procedure and `/* /*` comment out *EVERYTHING* after the procedure definition.


eg..



```
ALTER PROCEDURE dbo.Foo
(
    Bar1 INT,
    Bar2 TINYINT,
    ... // whatever u have as your optional input arguments //
)
AS
    SET NOCOUNT ON

    /* 
    .... every thing in here is commented out 
    */
GO

```

Now ...
 3. Add a forced fake return in the stored proc, which (more or less) just *defines* the output structure/fields.


eg..



```
ALTER PROCEDURE dbo.Foo
(
    Bar1 INT,
    Bar2 TINYINT,
    ... // whatever u have as your optional input arguments //
)
AS
    SET NOCOUNT ON

    SELECT 1 AS Id, 1 AS UserId, 1 AS SomeOtherId, 
        CAST('AAA' AS NVARCHAR(350)) AS Name,
        -- etc etc etc..
    /* 
    .... every thing in here is commented out 
    */
GO

```

[and then](http://www.youtube.com/watch?v=K7luMp6lb9M) ...


4. Add this stored proc to your EF designer/wizard/etc... Now the correct fields should be 'determined' by the designer. AWESOME. Yes .. the values are all hardcoded .. but that's ok (so far).
5. Once your happy that EF is now updated right, go back to your stored proc, and remove all hardcoded SELECT (which we did in the above step). Now we remove the comments which we commented out the entire real code. So you should have your original stored proc, back.


... and now EF is updated and doesn't know we've changed the plumbing of your stored proc.


win :)


does this work for ya? Change this line of code (sql query) into:



```
OleDbCommand cmd = new OleDbCommand(Stirng.Format("INSERT INTO CHILDREN2 (Emp_ID , Child_Name, Age) VALUES ({0}, {1}, {2})", Convert.ToInt32(CaptionText.Text),txtName.Text, txtAge), clsData.con);
```

Do you have a WHERE clause too? I removed it from your query, becasue it didnt belonge on that place where you put it.  

If you will use a Where clause then create an sql query this was:



```
"INSERT INTO TableName (Field1, Field2) VALUES (@paramField1, @paramField2) WHERE Field3 = @paramField3"
```

where params are parameters passed to the fields, and WHERE is a conditional clause, and has to be stated on the end (after specifying parameters). The basic idea here is that your adding properties to the model that EF can use to make a relationship. Right you're trying to create a relationship of type `User` and that is creating an error. To assign a composite key each key needs to be a type compatible with a `Key` field, not a navigation property. So we add `UserId` and `FriendId` of type `int`, `string` or `GUID` etc. and create a relationship off those properties.



```
public class Relationship
{
    public User Friend { get; set; }

    public User User { get; set; }

    public int FriendId { get; set; }

    public int UserId { get; set; }

    public DateTimeOffset RelationshipInitializationDate { get; set; }
}

public class User
{
    public int UserId { get; set; }
}

```

You can now define a composite key across `UserId` and `FriendId`. Something like this should do:



```
public class NorthwindContext : DbContext
{
     public NorthwindContext(DbContextOptions<NorthwindContext> options):base(options) { }

     public NorthwindContext() { }

     protected override void OnModelCreating(ModelBuilder builder)
     {
         builder.Entity<Relationship>().HasKey(table => new {
         table.FriendId, table.UserId
         });
     }
     public DbSet<Relationship> Relationships { get; set; }
     public DbSet<User> Users { get; set; }
}

```

[Source: Medium - How To: Entity Framework Core relationships, composite keys, foreign keys, data annotations, Code First and Fluent API](https://medium.com/radzenhq/how-to-entity-framework-core-relationships-composite-keys-foreign-keys-data-annotations-code-f4b238086463) **IMPORTANT NOTE:** You have to **sort your data** first.







The part I didn't get is that in the example construction



```
groups = []
uniquekeys = []
for k, g in groupby(data, keyfunc):
   groups.append(list(g))    # Store group iterator as a list
   uniquekeys.append(k)

```

`k` is the current grouping key, and `g` is an iterator that you can use to iterate over the group defined by that grouping key. In other words, the `groupby` iterator itself returns iterators.


Here's an example of that, using clearer variable names:



```
from itertools import groupby

things = [("animal", "bear"), ("animal", "duck"), ("plant", "cactus"), ("vehicle", "speed boat"), ("vehicle", "school bus")]

for key, group in groupby(things, lambda x: x[0]):
    for thing in group:
        print("A %s is a %s." % (thing[1], key))
    print("")
    

```

This will give you the output:



> 
> A bear is a animal.  
> 
> A duck is a animal.
> 
> 
> A cactus is a plant.
> 
> 
> A speed boat is a vehicle.  
> 
> A school bus is a vehicle.
> 
> 
> 


In this example, `things` is a list of tuples where the first item in each tuple is the group the second item belongs to.


The `groupby()` function takes two arguments: (1) the data to group and (2) the function to group it with.


Here, `lambda x: x[0]` tells `groupby()` to use the first item in each tuple as the grouping key.


In the above `for` statement, `groupby` returns three (key, group iterator) pairs - once for each unique key. You can use the returned iterator to iterate over each individual item in that group.


Here's a slightly different example with the same data, using a list comprehension:



```
for key, group in groupby(things, lambda x: x[0]):
    listOfThings = " and ".join([thing[1] for thing in group])
    print(key + "s:  " + listOfThings + ".")

```

This will give you the output:



> 
> animals: bear and duck.  
> 
> plants: cactus.  
> 
> vehicles: speed boat and school bus.
> 
> 
> not [full disclosure] a linux person per se.

something similar to the following should work:

https://www.unix.com/shell-programming-and-scripting/68166-converting-integer-string.html

start simple with just enough code to test the concept.

see if it works and modify/correct as necessary.

then if it still does not work, then post your documented code accordingly along an explanation of what is or is not happening as you expect or desire. hi

we can do that by defining a string variable in the very beginning of the process with assign activity or by defining that in variable panel in the studio at the bottom the window

but make sure that we are not assigning any value to that variable later across the workflow

if we assign later th… I have the same problem and added this to the DbContext:



```
private void PrintStackTrace()
{
  var st = new StackTrace();
  foreach (var sf in st.GetFrames())
    Console.WriteLine($"{sf.GetMethod().DeclaringType.Assembly.GetName().Name} {sf.GetMethod().DeclaringType.Name} {sf.GetMethod().Name}");
}

```

and call this function in the contructor and Dispose of the DbContext.


It seems to be a problem, if the DbContext is added with scoped lifetime to the dependency injection. I filed an issue for that.


<https://github.com/aspnet/EntityFrameworkCore/issues/10693> hi james,

as you already figured out, custom transactions does not support automatic instrumentations (xhr, fetch, resources) which means the user would have to start span and attach it to the transaction manually.

and yes by setting { managed: true } in the starttransaction option the transaction…