linq2sql: using ExceuteQuery<dto> when rows returned are not in my dto? Can i use a generic data type?

Question

hi there,

been using ExecuteQuery with some success, i.e. where AccessRights is my dto and queryString contains "Exec sp_name param1,param2 etc"

  var accessRights = 
    this.db.ExecuteQuery<AccessRights>(queryString, sqlParams.Values.ToArray()).AsQueryable();

Everything works perfect if what returns from the stored procedure can be mapped perfectly to the type (dto) that i pass in the generic ExecuteQuery

Problem is now i have a stored procedure that returns a non standard column name.

Basically my i hav my AccessRights class (dto) which contains, "userId", "accessRightId", "Description"

but the new stored procedure returns UserId, AccessRightId, "TemporaryDescription".

now i can't change it as other things depend on it... if I do

 var accessRights = 
    this.db.ExecuteQuery<AccessRights>(queryString, sqlParams.Values.ToArray()).AsQueryable();

then i don't see "TemporaryDescription", which i suppose is logical as it doesn't exist

What i need to do is map temporaryDescription back to description.

Any body has any idea how to do this?

Answer 1

You could try adding the [Column(...)] attribute; no idea if that'll work.

A few options that leap to mind:

• build a class that does map 1:1 (by name), and then translate this data (via Select, or a LINQ query) into your actual intended class
• write a wrapper SP that renames the column (not nice; you'd need a temp table, presumably forcing recompile due to DDL/DML interleave)
• drag the SP onto the data-context designer and rename the columns manually in the generated types (consdier this as an automated implementation of the first bullet)
• move (refactor) the interesting part of the SP into a UDF that you can call from your existing sp, and use the UDF directly from the data-context (drag the UDF onto the designer)