none
How to handle generated column in Linq to SQL dbml file

    Question

  • Hi All,

    Below is the method generating by dotnet in dbml file. It is a wrapper of Stored Procedure GetProducts which is present in our database.

    [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.GetProducts")]
    public ISingleResult<Products> GetProducts()
    {
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return ((ISingleResult<Products>)(result.ReturnValue));
    }

    Here we are getting combination of pivot table and normal columns of Product table.

    ProductID (from Product table)
    ProductName (from Product table)
    2005 (Generated field)
    2006 (Generated field)
    2007 (Generated field)

    but not sure how to handle this :(

    The problem is dbml file we only have ProductId & Product name because those are actual columns but 2005,2006,2007 are generated through stored procedure so they are not coming into result.


    We have many scenarios where we need this so please help me.

    Thanks in advance

    Thanks in advance

    • Edited by ather_13 Monday, December 19, 2011 3:28 AM
    Monday, December 19, 2011 3:21 AM

All replies

  • You can edit the dbml file to suit your needs as it is only a xml file.

    Regards

    Monday, December 19, 2011 8:01 PM
  • Thanks for your reply sir.

    Actually I already tried that but not sure what should be the return value and how to iterate it.

    Below is the sample which I tried but not sure how to get the values and iterate it as I dont have exact class with all columns.

    [global::System.Data.Linq.Mapping.FunctionAttribute(Name = "dbo.GetProducts"

    )]

     

    public Dictionary<string, object

    > GetProducts()

    {

    System.Collections.

    IEnumerable result = this.ExecuteQuery<System.Collections.IEnumerable>("exec dbo.GetProducts"

    );

     

    return

    result.GetEnumerator();

    }

    Monday, December 19, 2011 10:33 PM
  • If you don't know beforehand what type of object you are going to receive then you can return Object, if you don't know beforehand how many columns you will get then you can edit the dbml file on the fly but let me stress this: even if the column does not appear in the dbml file you can use it, the only drawback is that it won't show up in the designer.

    Regards

    Tuesday, December 20, 2011 3:02 PM
  • I am also thiniking its doable but not sure how....:(

    I can return object but then how to iterate the column names and there values ?

    Can you give an example or sample link where it is already done ?

    Tuesday, December 20, 2011 9:21 PM
  • If you don't know beforehand what type of object you are going to receive then you can return Object, if you don't know beforehand how many columns you will get then you can edit the dbml file on the fly but let me stress this: even if the column does not appear in the dbml file you can use it, the only drawback is that it won't show up in the designer.

    Regards

    I am looking for iteration of the columns which is mentioned by you (highlighted in bold), I am still not sure how the column iteration to be done, will that come as key pair or how they come in object ?

    As column count is unknow to me and I am not sure what type of object I am getting in return. I can't retrive values from it

    If possible give me some example or sample link.

    I hope I am able to explain this time what exactly I want.



    • Edited by ather_13 Wednesday, December 21, 2011 6:27 PM
    Wednesday, December 21, 2011 6:25 PM
  • Ok, as I understand it, you have some sp that produce some columns, right? And you are not sure what field will be in that column, hence you can't create an accurate mapping at compile time, right?

    You can make your own mapping of course but if you don't know what object to return then the only thing you can return is Object but then you lose strong typing.  The link I gave you was for a trick that uses var as it is strongly typed and circunvent anonymous type limitations.

    The others two things that come into mind that could help you is either an hybrid approach and storing the data in either a tuple or an arraylist (a tuple is better because is strongly type but it might not be the best choice in your scenario) or just using plain sql to manipulate that part of the data.

    Hmmm, I haven't found a sample yet, I'll try to find one and if I don't I'll try to make one for you but it might take me some time (maybe by the end of the week) as I'm fairly busy.

    Regards

    PS:  Let me know if there is something I misunderstood as my english is not that good

    Wednesday, December 21, 2011 6:40 PM
  • Yes you got it now what I want :)

    I am also trying to find out solution...will post it if I find out anything

    Please you also try and if you find out then please share with us.

    Friday, December 23, 2011 2:16 PM
  • Ok, hmmm, so, one alternative I found is that LINQ supports sp returning more than one type of object.

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

    Read this, Scott's article is very good and this may solve your problem.

    Regards

    Friday, December 23, 2011 3:18 PM
  • Thanks for your reply.

    Actually I already saw this link, It doesn't have something which I can use for dynamic column generations, all are static columns  :(

    But I will look into it again...not an issue.

    If you find any other info let me know.


    • Edited by ather_13 Friday, December 23, 2011 7:56 PM
    Friday, December 23, 2011 4:56 PM
  • Hi ather_13,

    Welcome to MSDN Forum

    Based on the issue, I have researched and found the same link with Serguey123's. I'm afraind I didn't find any other way can help you. I'll continuely research on it, and I'll come back as soon as possible if I find some other information.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, December 26, 2011 8:59 AM
  • Thanks Allen & Serguey123 for your responses.

    I am also doing research but didn't get anything yet will wait for your reply.

    Tuesday, December 27, 2011 2:29 PM
  • Hi ather_13,

    I have researched for a long time about the issue, but still didn't find anything can help. The link Sergury123 provided may the only way can retrieve more than one type object.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Thursday, December 29, 2011 2:00 AM