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).

Answer 1

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();

Answer 2

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);
        }
    }
}