none
LINQ to SQL Designer, Map modified SP to Existing Data Model Class RRS feed

  • Question

  • Hi,

     

                I have a Table, Docs that has 3 Fields: DocID, DocName, DocContent. The DocContent is rather large i.e. 10K to 20K. I am loading the Documents from a Stored Procedure and using the Visual Studio LINQ to SQL Designer  to map it to an existing DataModel Class: Doc.

     

                My Stored Procedure looks like:

     

     

     

    CREATE PROCEDURE DocsSelectAll
    
    AS
    
          SET NOCOUNT ON;
    
          SELECT DocID, DocName, DocContent
    
          FROM Docs
    
     
    
    RETURN
    
    

     

     

    As mentioned by ScottGu in his Blog at

    LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

     

    in Section named “Mapping the Return Type of SPROC Methods to Data Model Classes” I drag the DocsSelectAll SP into the Doc Class/Table in the LINQ to SQL Designer (ORM Designer) to automatically return instances of the Doc class with DocID, DocName, DocContent as properties. This so far works fine.

     

                Since DocContent is a bit large (i.e. 10K to 20K) I am wondering if there is a way to return it as an empty String. For example I attempted another Stored Procedure:

     

     

     

     

    CREATE PROCEDURE DocsSelectAllNoContent
    
    AS
    
          SET NOCOUNT ON;
    
          SELECT DocID, DocName
    
          FROM Docs
    
     
    
    RETURN
    
    

     

     

    If I attempt to drag this Stored Procedure into the Doc Class, I get “One or more selected database objects return a schema that does not match the schema of the target or data class.” This is true. This does not have the DocContent, while the Doc Class has a DocContent property. I can however drag the Stored Procedure to the Methods Pane, but this would create a new class DocsSelectAllNoContent – which I want to avoid.

               

                Is there anyway of solving this problem?  If my above explanation is not clear please let me know.

     

    Thanks in advance for your help,

    O.O.

    Wednesday, August 4, 2010 7:30 PM

Answers

  • As mentioned in my previous post, my modified DocsSelectAllNoContent Stored Procedure does not work when dragged into the Doc class in the LINQ to SQL Designer i.e. if you attempt to drag it you would get an error “One or more selected database objects return a schema that does not match the schema of the target or data class.”

     

                Since the structure and naming of variables is the same as the Doc Class, you can solve this problem by instead dragging the DocsSelectAllNoContent Stored Procedure to the Methods Pane of the O/R Designer. (It would obviously be mapped to a custom class – not the Doc class.) Click on the newly added Procedure in the Methods Pane, and in the Properties Window, under Return Type use the DropDown to change it from CustomClass to the Doc class. (The LINQ to SQL Designer would warn you – for which you select Yes.)

     

                I then tested my code and this works fine. I hope this helps someone.

     

    O.O.

    • Marked as answer by o-o-o-o Tuesday, August 10, 2010 10:04 PM
    Tuesday, August 10, 2010 10:04 PM
  • //Create a datacontext

    DataContext test=new DataContext();

    var myquery= from variable in test.Docs select new {DocID=variable.DocID, DocName=variable.DocContent};

    On the method part, you could redefine how the entity work and how return the data, you can add a method as this class is partial that will only return two of these items, something alike the sp you tried.

    Regards

    • Marked as answer by o-o-o-o Friday, August 6, 2010 5:09 AM
    • Unmarked as answer by o-o-o-o Sunday, August 8, 2010 2:40 AM
    • Marked as answer by o-o-o-o Sunday, August 8, 2010 2:40 AM
    Thursday, August 5, 2010 5:17 PM
  • I actually cross posted this to Select Statement with Default Value 

     

    The solution there suggested I use the following SP for DocsSelectAllNoContent instead of the one in the my original post above:

     

     

    CREATE PROCEDURE DocsSelectAllNoContent
    AS
        SET NOCOUNT ON;
       SELECT DocID, DocName, cast('' AS varchar(10)) AS DocContent
       FROM Docs
     RETURN
    
    

     

    The good thing about this modification is that it no longer gets DocContent from the DB improving performance, while being able to be mapped to the Doc class in the LINQ to SQL Designer (ORM Designer) since it "seems" to have the same schema.

     

    Thanks again to Serguey123 again for helping.

    O.O.

     

    • Marked as answer by o-o-o-o Friday, August 6, 2010 5:09 AM
    • Unmarked as answer by o-o-o-o Sunday, August 8, 2010 2:40 AM
    • Marked as answer by o-o-o-o Tuesday, August 10, 2010 10:04 PM
    Friday, August 6, 2010 5:09 AM

All replies

  • You could redefine the method of course but, you could also use a linq query to retrieve the data you need only instead of a sp that return everything

    Regards

    Thursday, August 5, 2010 1:07 PM
  • Dear Serguey123,

     

                Thanks for your response. I am not clear what you mean by “redefine the method” – I do not understand which method you are referring to. Are you referring to the Stored Procedure? I have attempted that in DocsSelectAllNoContent in my original post.

     

                Could you please provide an example of the LINQ Query that you are suggesting? I interested in a query that does not load the DocContent from the DB. I am not interested in a query that loads the DocContent – but excludes it after loading it.

     

    Regards,

    O.O.

    Thursday, August 5, 2010 5:09 PM
  • //Create a datacontext

    DataContext test=new DataContext();

    var myquery= from variable in test.Docs select new {DocID=variable.DocID, DocName=variable.DocContent};

    On the method part, you could redefine how the entity work and how return the data, you can add a method as this class is partial that will only return two of these items, something alike the sp you tried.

    Regards

    • Marked as answer by o-o-o-o Friday, August 6, 2010 5:09 AM
    • Unmarked as answer by o-o-o-o Sunday, August 8, 2010 2:40 AM
    • Marked as answer by o-o-o-o Sunday, August 8, 2010 2:40 AM
    Thursday, August 5, 2010 5:17 PM
  • Thanks Serguey123, I understand now what you meant. While I could certainly do that, I am wondering if I can do it with Stored Procedures and dragging that into the DBML Files. Right now this project uses SP’s and I would prefer it stay that way, unless there is no way out.

     

    O.O.

     

     

    Thursday, August 5, 2010 9:20 PM
  • I actually cross posted this to Select Statement with Default Value 

     

    The solution there suggested I use the following SP for DocsSelectAllNoContent instead of the one in the my original post above:

     

     

    CREATE PROCEDURE DocsSelectAllNoContent
    AS
        SET NOCOUNT ON;
       SELECT DocID, DocName, cast('' AS varchar(10)) AS DocContent
       FROM Docs
     RETURN
    
    

     

    The good thing about this modification is that it no longer gets DocContent from the DB improving performance, while being able to be mapped to the Doc class in the LINQ to SQL Designer (ORM Designer) since it "seems" to have the same schema.

     

    Thanks again to Serguey123 again for helping.

    O.O.

     

    • Marked as answer by o-o-o-o Friday, August 6, 2010 5:09 AM
    • Unmarked as answer by o-o-o-o Sunday, August 8, 2010 2:40 AM
    • Marked as answer by o-o-o-o Tuesday, August 10, 2010 10:04 PM
    Friday, August 6, 2010 5:09 AM
  • My Procedure in my post above does not work. I can swear I tried this when I posted above – but it does not seem to work now. I am starting a new thread on this topic.

    O.O.

    Sunday, August 8, 2010 2:42 AM
  • As mentioned in my previous post, my modified DocsSelectAllNoContent Stored Procedure does not work when dragged into the Doc class in the LINQ to SQL Designer i.e. if you attempt to drag it you would get an error “One or more selected database objects return a schema that does not match the schema of the target or data class.”

     

                Since the structure and naming of variables is the same as the Doc Class, you can solve this problem by instead dragging the DocsSelectAllNoContent Stored Procedure to the Methods Pane of the O/R Designer. (It would obviously be mapped to a custom class – not the Doc class.) Click on the newly added Procedure in the Methods Pane, and in the Properties Window, under Return Type use the DropDown to change it from CustomClass to the Doc class. (The LINQ to SQL Designer would warn you – for which you select Yes.)

     

                I then tested my code and this works fine. I hope this helps someone.

     

    O.O.

    • Marked as answer by o-o-o-o Tuesday, August 10, 2010 10:04 PM
    Tuesday, August 10, 2010 10:04 PM