locked
How to improve the Performance of LINQ to entity RRS feed

  • 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. 

                                                                                                           
    Monday, June 10, 2019 6:03 AM

All replies

  • 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

    Monday, June 10, 2019 7:19 AM
  • 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.
    Tuesday, June 11, 2019 6:05 AM
  • 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

    Tuesday, June 11, 2019 8:47 AM
  • 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

    Wednesday, June 12, 2019 5:21 PM