I'm stuck using EF5 on a legacy project. In it, I wish to return two fields from the database using some custom, "raw" Sql with EF. When I try and return the values into a named tuple, the values are always the 'default value' of the value-type. So it's not mapping the database value to the named tuple.

Does EF5 support this concept? If yes, could someone explain what I've done wrong, then?

const string userQuery = @"
SELECT Top 1 ThirdPartyIdentifier, ThemeId 
FROM Users
WHERE ThirdPartyIdentifier IS NOT NULL AND ThemeId > 0";

var (thirdPartyIdentifier, themeId) = SqlServerContext
    .Database
    .SqlQuery<(string ThirdPartyIdentifier, int ThemeId)>(userQuery)
    .Single();


#region Assembly EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
// C:\Users\pewpew\.nuget\packages\entityframework\5.0.0\lib\net45\EntityFramework.dll
// Decompiled with ICSharpCode.Decompiler 7.1.0.6543
#endregion

I've ran the query manually against the database and it does return some values. While the above only returns null and 0.

CodePudding user response：

Does EF5 support this concept?

• No, it does not.
• To my knowledge, all versions of Entity Framework and Entity Framework Core 1.x and 2.0 only support binding query-result columns to class types which must have parameterless constructors and mutable property setters (though they don't need to be public).
• Entity Framework Core 2.1 added support for "convention-based" entity class constructors, but (as of August 2022, in EF Core 6) still does not offer any way to configure how constructors are mapped.
• But crucially: you cannot map to fields, even exposed and mutable fields.
  • And ValueTuple only has fields. Joy.

Entity Framework uses the functionality from its inner ObjectContext.Translate to map from query results to registered/configured entity class types and non-table query results to complex-types (which are not embued with entity types' magic in DbContext but still useful nontheless).

• You can use "anonymous types" in a .Select projection in a Linq-to-EF query, but anonymous-types are pretty awful for other reasons - but it's often the only choice with EF.
• Before anyone says "use a record class!", unfortunately the OP isn't using C# 9.0, and there's still open issues with record types.

So you need to be a tad verbose with .Database.SqlQuery, but it isn't too bad: a simple mutable POCO with property-setters will work - if you can hold-your-nose about EF 5 disregarding constructors (*grumble*):

public class UserQueryResult
{
    public String ThirdPartyIdentifier { get; internal set; }
    public Int32  ThemeId              { get; internal set; }
}

const string userQuery = @"
SELECT
    TOP 1
    ThirdPartyIdentifier,
    ThemeId 
FROM
    Users
WHERE
    ThirdPartyIdentifier IS NOT NULL
    AND
    ThemeId > 0;
";

UserQueryResult result = await SqlServerContext
    .Database
    .SqlQuery<UserQueryResult>( userQuery )
    .SingleAsync( cancellationToken )
    .ConfigureAwait(false);