How to improve the Performance of LINQ to entity

Question

Hi

I'm using Entity framework 6.0. in app.config I'm using below connection string

 <connectionStrings>   

  <add name="TrekEntities" connectionString="metadata=res://*/TrekModel.csdl|res://*/TrekModel.ssdl|res://*/TrekModel.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string=&quot;DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= rb00692vmx.rb.de.conti.de)(PORT=1521))(CONNECT_DATA=(SID=trek)));PASSWORD=Trek2017;PERSIST SECURITY INFO=True;USER ID=TREK&quot;" providerName="System.Data.EntityClient" />

</connectionStrings>


and my Linq is written below

 using (var db = new TrekEntities())
                {


                    var testCaseParentList = (from specficationbStruc in db.SPECIFICATION_STRUCTURE

                                              join dsp in db.PROJECT_DS_PROJECT_MAPPING on specficationbStruc.SOURCE_PROJECT_ID equals dsp.DATA_SOURCE_PROJECT_ID
                                              join project in db.PROJECTs on dsp.PROJECT_ID equals project.ID

                                              where project.ID == projectID && specficationbStruc.SOURCE_OBJECT_LEVEL == 1

                                              && specficationbStruc.SOURCE_ARTEFACT_TYPE == artefactEnum && specficationbStruc.SOURCE_OBJECT_TYPE != "comment"

                                              && specficationbStruc.SOURCE_OBJECT_TYPE != null

                                              select new DatabaseGenericFlatData
                                              {
                                                  ID = specficationbStruc.ID,
                                                  DsComponentID = specficationbStruc.DATA_SOURCE_COMPONENT_ID,
                                                  DsComponentName = specficationbStruc.DATA_SOURCE_COMPONENT.NAME,
                                                  C_This_is_a = specficationbStruc.SOURCE_OBJECT_TYPE,
                                                  ObjectID = specficationbStruc.OBJECT_ID,
                                                  ObjectUiD = specficationbStruc.SOURCE_OBJECT_UID,
                                                  TestCaseParentId = specficationbStruc.SOURCE_OBJECT_PARENT_UID,
                                                  ArtefactHeading = specficationbStruc.SOURCE_OBJECT_HEADING,
                                                  ArtefactLevel = specficationbStruc.SOURCE_OBJECT_LEVEL


                                              }

                               ).ToList();




                    return testCaseParentList;
                }

I just want to know, How can i improve the performance of Execution of query, It is taking time. 

                                                                                                       

Answer 1

The links may help you.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/ef/performance-considerations

https://docs.microsoft.com/en-us/ef/ef6/fundamentals/performance/perf-whitepaper

Answer 2

Hi,
 As you gave the link to improve the performance, it is suggested that to write the compile query.

I'm trying to write the compile query like that 


 using (TrekEntities nwc = new TrekEntities())
            {
               
                
                var query =  CompiledQuery.Compile(( TrekEntities db) => (from arteType in db.ARTEFACT_TYPE

                                                                                      select new ArtefactType
                                                                                      {
                                                                                          ID = arteType.ID,
                                                                                          Name = arteType.NAME
                                                                                      })
                                                                                     );
            }

but I'm getting error "their is no implicit reference conversion from Continental.Trek.Data.TrekEntities to sysytem.data.entity.objects.ObjectContext"


Why because in entity framework TrekEntities context is inherited by DbContext not ObjectContext but CompiledQuery.Compile method accept only ObjectContext object.

So How I will implement CompiledQuery in this scenario.

Answer 3

Why because in entity framework TrekEntities context is inherited by DbContext not ObjectContext but CompiledQuery.Compile method accept only ObjectContext object.

So How I will implement CompiledQuery in this scenario.

In EF 6,  the queries are automatically compiled and there is no need to compile them.  The ObjectContext has been depreciated in EF 6 replaced by the Dbcontext. 

The link also talked about avoiding Linq queries with too many joins. 

If you are in fact using Oracle, then I would suggest that you execute an Oracle Stored Procedure using a Package producing a ToList() result.

https://stackoverflow.com/questions/21965605/how-to-call-a-stored-procedure-inside-an-oracle-package-with-entity-framework

Answer 4

Use a stored procedure and tweak the SQL there. It usually comes down the SQL when it comes to tuning this type of stuff.

---

william xifaras