locked
Entity Framework - map entity to sql query without ef designer? RRS feed

  • Question

  • User19694046 posted

    I have a model that needs to represent a query result  instead of a table, I think. 

    Here is the query I would use to get the data:

    ;with t1 as (
    select first_name, last_name, middle_name, record_id, record_name, instant_of_upd_tm
    , row_number() over (partition by record_name order by instant_of_upd_tm desc, record_id desc) as rn
    from names_hx
    )
    select emp.record_name
    , t1.first_name
    , t1.last_name
    , t1.MIDDLE_NAME
    , t1.RECORD_ID
    from t1
    join emp on t1.RECORD_name = emp.record_name
    where t1.rn = 1

    The names_hx holds the name changes over time.  The emp table's primary key is record_name.  The names_hx table primary key is record_id, with a foreign key record_name to the emp table.  The database already exists and the structure cannot be changed.  The goal is to have the employee model display the employee's data with the most current name. 

    Can an employee model be mapped to the results of this query in the DbContext class?  Or is their another way I should be materializing this data into my employee model? 

    I see there is something available with the EF designer called a defining query, but I was trying to stay away from using the designer.   Can I use the designer for this model, but keep the other poco models already in place?   

    Thank you!!

    Thursday, May 4, 2017 8:11 PM

Answers

  • User-1838255255 posted

    Hi Ygwywf,

    I can understand that you want to map the entity to SQL query without entity framework designer. As far as I know, you could create a new model and its properties have to match the names of the columns returned from the query. Declare a variable, then use the ‘Database.SqlQuery’ method with the specific model type and execute your SQL query sentence.

    The following code is the sample which includes model and method part.

    Here is the model part:

    public class temp
        {
            public int record_id { get; set; }
            public string record_name { get; set; }
            public string first_name { get; set; }
            public string last_name { get; set; }
            public string middle_name { get; set; }
        }
    

    Here is the method part:

    var result = db.Database.SqlQuery<temp>(
        "SELECT emps.record_name, first_name, last_name, middle_name, record_id " +
        "FROM names_hx " +
    "JOIN emps ON names_hx.record_name = emps.record_name");
    

    You can learn more information about this method in the following link<u1:p></u1:p>

    https://msdn.microsoft.com/en-us/library/system.data.entity.database.sqlquery(v=vs.113).aspx

     

    I hope it could be helpful.<u1:p></u1:p>

     

    Best Regards,<u1:p></u1:p>

    Eric Du<u1:p></u1:p>

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 5, 2017 10:02 AM