2

I have a database with multiple tables and some stored procedures that query if to get specific data from various tables in order to display desired information. Find one example of a stored procedure bellow:

SELECT DISTINCT             
    ROW_NUMBER() OVER (ORDER BY dbo.[table1].ReportedDate DESC) AS rowNumber,
    dbo.[table1].[id],
    dbo.[table1].caseReference,
    dbo.[table2].organisationName AS customerName,
    dbo.[table3].assetRegistration,
    dbo.[table4].surname
FROM dbo.[table1] WITH (NOLOCK)
LEFT JOIN   dbo.[table2] with (NOLOCK)
ON          dbo.[table2].JobId = dbo.[table1].[id]
LEFT JOIN   dbo.[table3] WITH (NOLOCK)
ON          dbo.[table3].id = dbo.[table2].[JobServiceId]
LEFT JOIN   dbo.[table4] WITH (NOLOCK)
ON          dbo.[table4].[jobID] = dbo.[table1].[id]
WHERE (table1.caseReference LIKE @caseReference+'%')

I want to move from using such stored procedures to a more code based approach using entity framework. How can I recreate a query like the one above using Linq query over the dbContext classes mapped to the database?

I am mostly having problems in figuring out how to choose the data I want to be returned from each table and how to put it all together.

Razvan Goia
  • 21
  • 1
  • 1
  • 4
  • for optimal performance and easy maintenance in future, I would prefer using stored procedures for complex logics – techspider May 11 '16 at 15:12
  • I tend to take the approach of letting EF write the SQL for me (basic ORM usage scenario) but create my own stored procedures when the SQL it generates is inefficient. Your example above would be an example of where I would use a stored procedure over EF generated SQL. – Mark Meisel May 11 '16 at 15:16
  • @techspider regarding performance, which will provide better results, using entity framework for such queries or stored procedures, keeping in mind that the example above is a cut down version of the ones in use – Razvan Goia May 11 '16 at 15:21
  • @RazvanGoia can you try with my answered . – Nazmul Hasan May 11 '16 at 15:22
  • @RazvanGoia there are plenty of articles out there differentiating the usage between EF or SP. for example see this http://stackoverflow.com/questions/14530/linq-to-sql-vs-stored-procedures. One medicine can't be cure for all diseases!! Writing code is not that important; think of security, maintainability, ease of support. – techspider May 11 '16 at 15:29

1 Answers1

2

you can do it easly Linq query or lambda expressions over the dbContext classes mapped to the database.

check this it would be help you
Entity Framework Join 3 Tables
How to join multiple tables?

Community
  • 1
  • 1
Nazmul Hasan
  • 10,130
  • 7
  • 50
  • 73