Asked by:
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="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"" 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