none
LINQ to SQL - Stored Procedure or View? RRS feed

  • Question

  • I have an asp.net application that utilizes LINQ to SQL.  I need to return a "table" of data that will require additional filtering (querying) once the application receives the data.  I seem to have 2 options.

    1. I can call a stored procedure and then query the results from the stored procedure via LINQ.  This has the benefit of being able to pass parameters to the SP so that it returns a smaller dataset.

    2. I can query a View on the SQL Server (2005) via LINQ, but will have to have a much larger dataset in the View to accomodate different query situations.

    I am interested in any suggestions.  We could also do this with LINQ to Entities if anyone thinks that we gain something by using EF over LINQ to SQL.

    Thanks in advance.

    Friday, December 18, 2009 7:06 PM

Answers

  • Hello,

     

    Welcome to MSDN Forums!

     

    In my opinion, the two options that you have listed both have the merits and drawbacks.  You can select them based on your detailed scenario. 

     

    For option 1:  As you have said, using stored procedure with parameters, we can get a smaller dataset each time.  But it also increases the times to access the database. 

     

    For option 2:  The first time to access the database may take longer time because a much larger dataset is returned, however, once the data is returned we can use general LINQ to Objects to query the data in memory.  We also need to pay attention to the data volume because if the dataset is really large, it could be huge pressure on the application memory. 

     

    Both LINQ to SQL and EF can directly query the views, so could you make a LINQ to SQL or LINQ to Entities query (with filter) on the view?  I think it might be more flexible. 

     

    Besides, LINQ to SQL and EF are both fine to your application.  But I perform EF because it is more powerful and in EF4 (.NET 4.0), we have much improvements.  Please see ADO.NET team blog for detailed information: http://blogs.msdn.com/adonet/default.aspx. 

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 21, 2009 1:51 AM
    Moderator

All replies

  • Hello,

     

    Welcome to MSDN Forums!

     

    In my opinion, the two options that you have listed both have the merits and drawbacks.  You can select them based on your detailed scenario. 

     

    For option 1:  As you have said, using stored procedure with parameters, we can get a smaller dataset each time.  But it also increases the times to access the database. 

     

    For option 2:  The first time to access the database may take longer time because a much larger dataset is returned, however, once the data is returned we can use general LINQ to Objects to query the data in memory.  We also need to pay attention to the data volume because if the dataset is really large, it could be huge pressure on the application memory. 

     

    Both LINQ to SQL and EF can directly query the views, so could you make a LINQ to SQL or LINQ to Entities query (with filter) on the view?  I think it might be more flexible. 

     

    Besides, LINQ to SQL and EF are both fine to your application.  But I perform EF because it is more powerful and in EF4 (.NET 4.0), we have much improvements.  Please see ADO.NET team blog for detailed information: http://blogs.msdn.com/adonet/default.aspx. 

     

    If you have any questions, please feel free to let me know.

     

    Have a nice day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 21, 2009 1:51 AM
    Moderator
  • Hello,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 24, 2009 12:55 AM
    Moderator
  • Your response was helpful.  I was hoping there was an accepted "best practice" for this scenario, but I think you have outlined the advantages of both.

    Thanks

    Monday, December 28, 2009 5:53 AM
  • It's my pleasure!


    If you have any questions, please feel free to let me know.

    Happy New Year! 

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 28, 2009 6:52 AM
    Moderator