locked
Linq2Sql SP w/ multiple results - adding an additional property from other table RRS feed

  • Question

  • I have a procedure  (complex logic not for linq)that's returning multiple resultsets and I have this .net attribute added to the DataContext method that calls the SP:

    [ResultType(typeof(Customer))]

     

    But I need to add in a CreatedByName (which is in a different table). If I join to that table in the procedure and name the selected column CreatedByName, how can I get Linq2Sql to map this for me or how could I tell it to map it correctly for me?

     

    I tried doing an inheritance thing on the model  to define a new Customer type CustomerWithCreatedBy and added the new column as well as change that other attribute to

    [ResultType(typeof(CustomerWithCreatedBy))], but that didn't help and gave me an error..  "CustomerID is not part of the mapping for type 'CustomerWithCreatedBy'. Is the member above the root of an inheritance hierarchy?"

     

    With all of this headache I'm thinking I may have to go back to non Linq data access because right now I can't even get at the additional column in the results.

    Wednesday, March 28, 2012 8:44 PM

All replies

  • Hi ryanb2009,

    Welcome!

    You can use LINQ to SQL to handle multiple result shapes from Stored procedure:

    http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx 

    You can put the Type which contains "CreateByName" property to the stored procedure as well.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 29, 2012 8:11 AM
  • Thanks, Alan for your interest in my questions and for providing the link. I have seen that article before and I don't believe it covers my situation. I'm not necessarily dealing with different result shapes in the return. I'm always going to have the same shapes, I just need 1 or 2 more columns added onto an existing entity class (which aren't in the real table). I thought I could quickly do this with inheritance but the engine complains about missing attributes on the Customer table columns.

    Did you see that I've already added a new type to the model (CustomerWithCreatedBy) and added the new property CreatedByName to that type, and then also changed the .net SP method to have attribute of [ResultType(typeof(CustomerWithCreatedBy))]
    but then it fails and complains about the CustomerID property which I did not change in the derived class at all.

    Error message:
    "CustomerID is not part of the mapping for type 'CustomerWithCreatedBy'. Is the member above the root of an inheritance hierarchy?"


    Is it expecting me to redefined and reattribute CustomerID in the derived class? I really want to leave Customer properties as is and not redefine anything (otherwise it would end up in double maintenance because I'd be maintaining 2 classes - Customer and CustomerWithCreatedBy).

    Now just thinking out loud here: In the inherited class, did the attributing that Linq2Sql already did on the Customer class not be carried over? Is that the problem maybe?


    I wonder how we could solve that... I see the .net documentation says that Attributes should be inherited by default if it's not defined (and it doesn't look like that's defined on ColumnAttribute). I did find a forum post that mentions GetCustomAttributes and it makes me think that MS used that method and the inherited set to false when developing the Linq2Sql internals. This is probably making the CustomerID column not seem mapped in my CustomerWithCreatedBy type.

    So you see where I'm at? I just wanted to add ONE, just one ;) more property to the class that exists in another table (otherwise I'd add it to the Customer type straight up). And it is taking up so much time just to try to figure out how to add that one property onto the class. :(

    Another alternative that I tried is adding the property to the Customer class on the model (even though the CreatedByName value is in another table), but then I have problems with SELECTing Customers because it's including the new property in the select statement.



    • Edited by ryanb2009 Thursday, March 29, 2012 4:23 PM
    Thursday, March 29, 2012 4:20 PM
  • Hi everyone! I'm still wondering if people had an idea how to have a few additional properties on an entity class that wouldn't be used for inserts/updates but just selects (and only if the columns are available). I can redefine an entire new entity for this and just use it for selects, but then it's double maintenance if a column is added - I'd have to add to both.

    Can inheritance be used somehow? See my previous posts for issues with the autogenerated attributes not being grabbed from the inherited class I tried.

    Tuesday, April 3, 2012 3:04 PM