Feature Request: Adding Support for Tuples in DbContext.Database.SqlQuery<T>()

[fedpo2]

Currently, Entity Framework does not support using tuples as the generic type in the DbContext.Database.SqlQuery<T>() method. This limitation makes it challenging to execute SQL queries that return columns that do not directly map to entities or predefined models in the code.

I would like Entity Framework to support tuples as the generic type in DbContext.Database.SqlQuery(). For example:

var results = context.Database.SqlQuery<(int Id, string Name)>("SELECT Id, Name FROM TableName");

This would allow developers to work directly with strongly-typed tuple results, simplifying development and eliminating the need for additional classes to handle temporary or specific query results.

[roji]

One limitation of value tuples, is that the field names (Id, Name) aren't preserved, so it wouldn't be possible to match the tuple fields to the columns coming back from the database. We could match positionally though.

[cincuranet]

I personally think records are better fit for this type of querying.

[roji]

@cincuranet I agree records are a good match here, though they do require an explicit type definition, which is somewhat annoying for an ad-hoc SQL query...

On the other hand, positional result binding - which is the only thing we can do with tuples - carries a certain degree of confusion that may make it a pit of failure. For example:

var results = context.Database.SqlQuery<(string Bar, string Foo)>("SELECT Foo, Bar FROM TableName");

In the code above, the natural expectation is for Foo on the returned tuple to contain the Foo value from the database, but it will not: since we don't have field names on the tuple, we can only bind positionally, meaning that Bar gets Foo, and Foo gets Bar.

Let's discuss this briefly in design. I'm not sure this would be a good idea.

[cincuranet]

FWIW there's TupleElementNames attribute produced by compiler. But also, tuple elements are usually lowercase. 🤷‍♂️

[roji]

FWIW there's TupleElementNames attribute produced by compiler. But also, tuple elements are usually lowercase. 🤷‍♂️

I don't think that works for the generic invocation usage above - just like we can't know about reference nullability of generic parameters... There's nowhere for the compiler to emit [TupleElementNames]...

[cincuranet]

Right. We're not in a place where it is declared. Pity. More points for records! :D