Entity Framework C# queries from strings

Question

Is it possible to get elements in a sqlite table from a string with the name of the table (using Entity Framework)? How?

And how can I get only the value of a property? (I need to get a list of IDs to create a in html that's used to choose which element in a table the user wants to delete)

using using Microsoft.EntityFrameworkCore;

public static List<string> GetAllIdsFromTableName(string tableName)
    {
        var db = new dbContext();

        // What I would like to do: 
        // return db.tableName.Select(x => x.id).ToList<string>();
    }

Yes, it is possible. But give more details. What is your table structure? Basically, expression which you wrote could be valid, if your table has entities which have id. — kosist
– kosist, Commented Jul 25, 2022 at 14:12
@kosist all my entities have an id which is also the primary key and it's always the fist field in the table. i need to do this so i can call a generic function which returns all the ids to create a <select> in the html page — Juri Mason
– Juri Mason, Commented Jul 25, 2022 at 14:31
var ids = context.Database.SqlQuery<string>($"Select id from {table}").ToList(); ? — Firo
– Firo, Commented Jul 25, 2022 at 14:44
returns this error: "CS1061: 'DatabaseFacade' does not contain a definition for 'SqlQuery' and no accessible extension method 'SqlQuery' accepting a first argument of type 'DatabaseFacade' could be found (are you missing a using directive or an assembly reference?)" — Juri Mason
– Juri Mason, Commented Jul 25, 2022 at 15:30

Svyatoslav Danyliv · Accepted Answer · 2022-07-25 17:55:56Z

0

The following extension returns IQueryable<string> and you can materialise arrays, lists, or you can do it asynchronously:

var result = context.GetAllIdsFromTable("SomeTable", "Id")
    .ToList();

And implementation:

public static class QueryableExtensions
{
    private static readonly MethodInfo _toStringMethod = typeof(Convert).GetMethods()
        .Single(m =>
            m.Name == nameof(Convert.ToString) && m.GetParameters().Length == 1 &&
            m.GetParameters()[0].ParameterType == typeof(object)
        );

    public static IQueryable<string> GetAllIdsFromTable(this DbContext ctx, string tableName, string idColumnName = "Id")
    {
        var model = ctx.Model;

        var entityType = model.GetEntityTypes().FirstOrDefault(et =>
            tableName.Equals(et.GetTableName(), StringComparison.InvariantCultureIgnoreCase));

        if (entityType == null)
            throw new InvalidOperationException($"Entity for table '{tableName}' not found.");

        // GetColumnName() can be obsolete, it depends on EF Core version.
        var prop = entityType.GetProperties().FirstOrDefault(p =>
            idColumnName.Equals(p.GetColumnName(), StringComparison.InvariantCultureIgnoreCase));

        if (prop == null)
            throw new InvalidOperationException($"Property for column '{tableName}'.'{idColumnName}' not found.");

        var entityParam = Expression.Parameter(entityType.ClrType, "e");
        var ctxParam = Expression.Parameter(typeof(DbContext), "ctx");

        // ctx.Set<entityType>()
        var setQuery = Expression.Call(ctxParam, nameof(DbContext.Set), new[] { entityType.ClrType });

        Expression propExpression;
        if (prop.PropertyInfo == null)
            // 'prop' is Shadow property, so call via EF.Property(e, "name")
            propExpression = Expression.Call(typeof(EF), nameof(EF.Property), new[] { prop.ClrType },
                entityParam, Expression.Constant(prop.Name));
        else
            propExpression = Expression.MakeMemberAccess(entityParam, prop.PropertyInfo);

        propExpression = EnsureString(propExpression);

        // e => e.Prop
        var propLambda = Expression.Lambda(propExpression, entityParam);

        // ctx.Set<entityType>().Select(e => e.Prop)
        Expression selectAll = Expression.Call(typeof(Queryable), nameof(Queryable.Select),
            new[] { entityType.ClrType, typeof(string) },
            setQuery, Expression.Quote(propLambda));

        var constructQuery = Expression.Lambda<Func<DbContext, IQueryable<string>>>(selectAll, ctxParam);

        return constructQuery.Compile()(ctx);
    }

    private static Expression EnsureString(Expression expression)
    {
        if (expression.Type == typeof(string))
            return expression;

        if (expression.Type != typeof(object))
            expression = Expression.Convert(expression, typeof(object));

        expression = Expression.Call(_toStringMethod, expression);

        return expression;
    }
}

answered Jul 25, 2022 at 17:55

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

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

4 Comments

Juri Mason Over a year ago

Thank you so much. this is great! i just need to adapt it a bit to my code but it already works fine

Juri Mason Over a year ago

could i use it also to remove elements some way?

Svyatoslav Danyliv Over a year ago

For removing it is need another code. Better to create new question with more details.

Juri Mason Over a year ago

Here's the new question: stackoverflow.com/questions/73119924/…

Collectives™ on Stack Overflow

Entity Framework C# queries from strings

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related