none
SqlQuery does not use the DataAnnotation of my POCO class. RRS feed

  • Question

  • I'm having issue on using the SqlQuery to submit my custom query.  The SqlQuery does not seems to use my DataAnnotation when filling the object by the data reader. I created a simple scenario just to show this issue.

     

    public class EFTestContext : DbContext
      {
        public DbSet<StudentEntity> Students { get;set; }
    
        public EFTestContext()
        {
          Configuration.AutoDetectChangesEnabled = false;
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
          modelBuilder.Entity<StudentEntity>().ToTable("STUDENT_TEST", "TEST");
        }
    
      }
        
      public class StudentEntity
      {
        [Column("STUDENT_ID")]
        public decimal Id { get; set; }
    
        [Column("FIRST_NAME")]
        public string FirstName { get; set; }
    
        [Column("LAST_NAME")]
        public string LastName { get; set; }
    
        [Column("LONG_NAME")]
        public string TestingForLongPropertyNameInPOCOClass { get; set; }
      }
    

    Sample Use:

     int count; 
          //-- Capturing the SQL generated by LINQ, this is the generated query:   
          //   SELECT 
          //     "Extent1"."STUDENT_ID" AS "STUDENT_ID", 
          //     "Extent1"."FIRST_NAME" AS "FIRST_NAME", 
          //     "Extent1"."LAST_NAME" AS "LAST_NAME", 
          //     "Extent1"."LONG_NAME" AS "LONG_NAME"
          //   FROM "TEST"."STUDENT_TEST" "Extent1"
          var result = context.Students;
          count = result.Count();
    
          //-- Using the same generated SQL from LINQ and pass it 
          //-- to the SqlQuery does not work and will throw exception:
          //-- The data reader is incompatible with the specified 'EfTest.WebUI.DAL.StudentEntity'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.
          var query = @"
            SELECT 
              Extent1.STUDENT_ID AS STUDENT_ID, 
              Extent1.FIRST_NAME AS FIRST_NAME, 
              Extent1.LAST_NAME AS LAST_NAME, 
              Extent1.LONG_NAME AS LONG_NAME
            FROM TEST.STUDENT_TEST Extent1
            ";
          var result2 = context.Students.SqlQuery(query);
          count = result2.Count();
    
    
          //-- So I modify the query to use the alias name which will matches to my POCO property name.
          //-- But this will not work on ORACLE if incase I have long propertyName (> 30 characters)
          //-- Oracle columnname or alias cannot have more than 30 characters in length.
          var query2 = @"
            SELECT 
              Extent1.STUDENT_ID AS Id, 
              Extent1.FIRST_NAME AS FirstName, 
              Extent1.LAST_NAME AS LastName, 
              Extent1.LONG_NAME AS TestingForLongPropertyNameInPOCOClass
            FROM TEST.STUDENT_TEST Extent1
            ";
          var result3 = context.Students.SqlQuery(query2);
          count = result3.Count();
    

    My question is why the SQLQuery fills the object differently compare to the LINQ-SQL. Why it does not use my DataAnnotation.

    In order to solve this issue on our application, I created a class derived from DBContext and extend its functionality which basically add one more generic methods (GetSQLQuery<entity>). This method uses my POCO DataAnnotation and it also able to tolerate unmap  reader field to my properties. SqlQuery will requires your query to specifiy all the fields.

     

    Friday, March 25, 2011 9:27 PM

Answers

  • Thank Rowan for the response. I'm afraid that will be the answer.

    Aliasing the column name will have limitation on the ORACLE as you can only name your property less than 30 characters in length. I know you might ask why do I name my property that long. Actually I did not name my property with that length, it just happen when I create my T4 text templating to generate my POCO class from the database and apply all my rules in naming (like FIRST_DE become FirstDescription or CREATE_DT as CreateDate), one of the property name resulted more than 30 characters in length.

     

    Anyway I just develop helper class which can populate my entity using custom query and still use the data annotation of my entity.

    I shared the code on this link:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/3cc5c0b0-bfda-41eb-9d47-e12657c81b3a/#9640b0f0-4cc1-409f-a740-53098878f662

     

     

     

    Friday, March 25, 2011 10:18 PM

All replies

  • Hi,

    The SqlQuery method bypasses all the mapping of EF and directly binds the results of a query to the object (based on column names matching the property names). Aliasing the column name in the query is the correct way to this.

    ~Rowan

    Friday, March 25, 2011 9:57 PM
    Moderator
  • Thank Rowan for the response. I'm afraid that will be the answer.

    Aliasing the column name will have limitation on the ORACLE as you can only name your property less than 30 characters in length. I know you might ask why do I name my property that long. Actually I did not name my property with that length, it just happen when I create my T4 text templating to generate my POCO class from the database and apply all my rules in naming (like FIRST_DE become FirstDescription or CREATE_DT as CreateDate), one of the property name resulted more than 30 characters in length.

     

    Anyway I just develop helper class which can populate my entity using custom query and still use the data annotation of my entity.

    I shared the code on this link:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/3cc5c0b0-bfda-41eb-9d47-e12657c81b3a/#9640b0f0-4cc1-409f-a740-53098878f662

     

     

     

    Friday, March 25, 2011 10:18 PM