locked
Map Stored Procedure Column names to POCO / DTO RRS feed

  • Question

  • User269881539 posted

    My stored procedure returns columns with names I don't want to use in my classes :

    SELECT c.ID,
    c.Name AS Category,
    c.ParentID, 
    sup.Distance,
    sup.Node_Seq,
    FROM Category c 
    INNER JOIN fnCategorySuperiors(@CategoryID) sup ON sup.ID = c.ID
    

    In my class I want different property names of CategoryID (not ID) and CategoryName (not Category) - the SP does not return those :

    public class Category
    {
    
        public int CategoryID { get; set; }
        public int ParentID { get; set; }
        public string CategoryName { get; set; }
    
        public IList<Category> SubCategories { get; set; }
        public IList<Category> Breadcrumb { get; set; }
    }

    How do I map this between SP and class - I thought I could add data annotations but whilst that seems to work for tables, for SPs I am not successful :

    [Column("ID", TypeName = "int")] //map column name
    public int CategoryID { get; set; }
    [Column("Category", TypeName = "varchar")] //map column name
    public string CategoryName { get; set; }
    

    I am populating my class like so :

    public List<Category> Categories(int id = 0)
    {
      //setup params
      var pId = new SqlParameter { ParameterName = "CategoryID", Value = 0 };
      //setup stored procedure
      string query = "EXEC prcCategoryList @CategoryID";
      //perform query
      var cats = context.Database.SqlQuery<Category>(query, pId).ToList();
      return cats;
    }
    

    Is there a way to map between them, or do I have to follow the SP column names in my code?

    Monday, December 14, 2015 5:32 PM

Answers

  • User-821857111 posted

    You can create a class (MyInterimObject) that has properties that match the schema returned by the SP and then return a collection of those. Then you can project those into Category objects. Something along the lines of:

    var result = context.Database.SqlQuery<MyInterimObject>(query, pId).ToList();
    var cats = result.Select(c => new Category{
        CategoryID = c.Id,
        ParentID = c.ParentID,
        CategoryName = c.Category
    }).ToList();

    If you are likely to map from MyInterimObject to Category on numerous occasions in code, you can use AutoMapper of similar to set up the mapping. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 14, 2015 7:44 PM