Raw queries with overridden column names

Question

I'm trying to retrieve some entities using Entity Framework by querying an XML column. Entity Framework doesn't support this so I had to use raw SQL.

var people = context.People.SqlQuery("SELECT * FROM [People] WHERE [DataXML].value('Properties/Age', 'int') = 21").AsQueryable().AsNoTracking();

My person class:

public class Person
{
    public int Id { get; set; }

    public string Name { get; set; }

    [Column("YearsSinceBirth")]
    public int Age { get; set; }

    [Column(TypeName = "xml")]
    public string DataXML { get; set; }
}

This should work, however, it falls over when trying to map it back to an object. Specifically, it's falling over on the Age property, which has it's column name overridden to "YearsSinceBirth".

'The data reader is incompatible with the specified 'MyProject.CodeBase.DataModel.DbEntities.Person'. A member of the type, 'Age', does not have a corresponding column in the data reader with the same name.'

I'm guessing that Entity Framework doesn't map database column names to object property names and therefore is expecting the column to be named 'Age' rather than 'YearsSinceBirth'.

I don't want to have to list each column and their mapping in the SQL query (like SELECT YearsSinceBirth As Age) as the actual project I'm working on which has this column has a lot more columns and that would mean this query would break every time the schema changed (kinda defeating the purpose of Entity Framework).

You're going to have to specify the column names I'm afraid, either that or create another class that matches the names you want to use. — DavidG, Feb 05 '18 at 16:20
Because you are doing `context.People.SqlQuery("Select *....")` EF is likely not using the attributes, I believe the attribute would used if you queried like `context.People.Where(p => p.DataXml ....)` — Vidmantas Blazevicius, Feb 05 '18 at 16:20
@VidmantasBlazevicius Yeah, unfortunately I can't as Entity Framework doesn't support querying XML on the database — Keir Nellyer, Feb 05 '18 at 16:27
@KeirNellyer any options to write a stored procedure or function to encapsulate this? — Vidmantas Blazevicius, Feb 05 '18 at 16:31
If I do need to declare the column names, is there anyway to dynamically generate them? I feel using reflection and checking for the [Column] attribute is a bit too simple and EF sometimes uses column names with an underscore if it doesn't have a mapping defined. — Keir Nellyer, Feb 05 '18 at 16:38
@VidmantasBlazevicius Would that get around the mapping issue? — Keir Nellyer, Feb 05 '18 at 16:42
See if this helps https://stackoverflow.com/questions/39779270/dynamic-translate-to-avoid-c-sharp-syntax-errors/39798961#39798961 — Ivan Stoev, Feb 05 '18 at 17:30

David Browne - Microsoft · Answer 1 · 2018-02-05T17:36:52.700

If this is EF Core, your problem is not that SqlQuery() doesn't support mapping column names (it does). Rather your problem is that your table doesn't contain a column called YearsSinceBirth, and you are returning 'select *'.

If you have a column called YearsSinceBirth, this works fine. Although you will be retrieving the value in the YearsSinceBirth column, not the value in the XML document. EG

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
//using Microsoft.Samples.EFLogging;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Data.SqlClient;

namespace EFCore2Test
{


    public class Person
    {
        public int Id { get; set; }

        public string Name { get; set; }

        [Column("YearsSinceBirth")]
        public int Age { get; set; }

        [Column(TypeName = "xml")]
        public string DataXML { get; set; }
    }

    public class Location
    {
        public string LocationId { get; set; }
    }

    public class Db : DbContext
    {
        public DbSet<Person> People { get; set; }
        public DbSet<Location> Locations { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=(local);Database=EFCoreTest;Trusted_Connection=True;MultipleActiveResultSets=true");
            base.OnConfiguring(optionsBuilder);
        }
    }




    class Program
    {


        static void Main(string[] args)
        {

            using (var db = new Db())
            {
                db.Database.EnsureDeleted();
                //db.ConfigureLogging(s => Console.WriteLine(s));
                db.Database.EnsureCreated();

                var p = new Person()
                {
                    Name = "joe",
                    Age = 2,
                    DataXML = "<Properties><Age>21</Age></Properties>"
                };
                db.People.Add(p);
                db.SaveChanges();
            }
            using (var db = new Db())
            {
                var people = db.People.FromSql("SELECT * FROM [People] WHERE [DataXML].value('(/Properties/Age)[1]', 'int') = 21").AsNoTracking().ToList() ;

                Console.WriteLine(people.First().Age);

                Console.ReadLine();
            }

            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}

You can use a pattern similar to this to project entity attributes from an XML or JSON column:

public class Person
{
    private XDocument xml;

    public int Id { get; set; }

    public string Name { get; set; }

    [NotMapped]
    public int Age
    {
        get
        {
            return int.Parse(xml.Element("Properties").Element("Age").Value);
        }
        set
        {
            xml.Element("Properties").Element("Age").Value = value.ToString();
        }
    }

    [Column(TypeName = "xml")]
    public string DataXML
    {
        get
        {
            return xml.ToString();
        }
        set
        {
            xml = XDocument.Parse(value);
        }
    }
}

As I see, you are assuming EF Core - based on what? EF6 indeed does not support column name mapping and requires query to return column names matching the property names. — Ivan Stoev, Feb 05 '18 at 17:21
Aah. DbSet.SqlQuery threw me off, as I always use Database.SqlQuery in EF6. — David Browne - Microsoft, Feb 05 '18 at 17:35

Slava Utesinov · Answer 2 · 2018-02-06T07:07:50.597

You can dynamically create select query with aliases, if they needed, with the help of reflection and ColumnAttribute checking:

public string SelectQuery<T>() where T : class
{
    var selectQuery = new List<string>();
    foreach (var prop in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance))
    {
        var attr = prop.GetAttribute<ColumnAttribute>();
        selectQuery.Add(attr != null ? $"{attr.Name} as {prop.Name}" : prop.Name);
    }
    return string.Join(", ", selectQuery);
}

Usage:

var people = context.People.SqlQuery($"SELECT {SelectQuery<Person>()} FROM [People] WHERE [DataXML].value('Properties/Age', 'int') = 21")
                    .AsQueryable().AsNoTracking();

Raw queries with overridden column names

2 Answers2