I've created a SQL query that I execute with the following command, and it returns the correct number of entries but these contains all 0:
If I run the same SQL command in my Management Studio, it works correctly.
I also tried it with a Linq statement and it works also correctly:
I hope you guys can help me to solve the problem.
You shouldn't be projecting into a List<T> - the ToList() does that. Basically, simplify:
var data = dbContext.Database.SqlQuery<Tuple<DateTime, string, string>>(...).ToList();
It might also work with value-tuples:
var data = dbContext.Database.SqlQuery<(DateTime, string, string)>(...).ToList();
which would also allow you to conceptually name them:
var data = dbContext.Database.SqlQuery<(DateTime Datum, string Text, string Bemerkung)>
(...).ToList();
Note: concatenating filter is almost certainly a SQL injection vulnerability; if looks like you should be using a SQL parameter there instead.
@Evk notes that EF might not support column-wise binding of tuples. If that is the case, then your best bet would be to create a POCO that matches the column definitions:
class Foo // rename me to something meaningful
{
// note: there may be custom attributes you can use
// to make these names less ugly, i.e.
// [Column("TEXT")] on a property called Text
public DateTime RMA_DATUM {get;set;}
public string TEXT {get;set;}
public string BEMERKUNG {get;set;}
}
and use SqlQuery<Foo>.
Tuple doesn't have default constructor. ValueTuple does, but anyway it has properties with names like Item1 etc, while columns returned by OP select statement have different names.
filterdirectly into the query string. That may or may not be all that's required - it's hard to tell with relatively little schema or type information.