Last post Dec 19, 2016 01:52 PM by jayakumarvinayagam
Dec 16, 2016 07:13 AM|jayakumarvinayagam|LINK
There are any specific steps we have to follow before getting into LINQ syntax. we faced some performance issues while moving our interaction in Production.
Just assumes, have 5 table with respective relation and each table has 5 million records. we got some slowness while making the join to get a result.
perhaps got an error while filtering custom type join with dbContext type.
Solving Error - Unable to Create a Constant Value of type 'System.Object' Only Primitive Types or Enumeration Types are Supported in this Context
Mostly we prefer Linq Methods syntax instead of Query syntax.
Dec 16, 2016 07:57 AM|PatriceSc|LINK
Seems you are using an object variable (rather than an int or whatever) in your Linq querry. The error message is that the translation to SQL fails as LINQ doesn't know how to handle an "object" variable.
Are you using "Include" (it does additional joins to load related data using a single query) ? Else it is always best to analyze what happens rather than trying randoom tips hoping they'll fix your issue. You could capture and analyze the underlying SQL
statement (using query.ToString() or your usual SQL side profiler and performance tools).
Also it is quite easy to load more data than needed. I would check how many items are found in the Linq context after this query (DbContext.MyDbSet.Local.Count for example) to see if it is expected. In some cases it is really that you load what you think
you need and it is still too many.
Not directly related but with Linq you have an object view and there is likely little point in nsuing explicit joins ( usually you are using "navigation properties"). To me it is useful only if the corresponding fk relation is not explictely defined for
Dec 16, 2016 09:18 AM|navneetmitawa|LINK
Try this ...
LINQ to Entity is a great ORM for querying and managing database. It offers a lot of things, so it is mandatory to know about performance of it. These are right up to a certain point as LINQ comes with its own penalties. There are some tips and tricks that
we should keep in mind while desiging and query database using entity framework ORM. Here is a list of some tips that I would like to share with you.
Entity Model specifies a single unit of work, not all our database. If we have many database objects that are not connected to one another or these(log tables, objects used by batch processes,etc.) are not used at all. Hence these objects are consuming space
in the memory and cause performance degrades. So try to make separate entity models of related database objects.
Whenever you retrieve the data only for reading purpose, not for modification then there is no need of object tracking. So disable object tracking by using MergeOption as below:
This option allow us to turn off the object cache and unnecessary identity management of the objects.
When the object of ObjectContext is created first time in the application, the entity framework creates a set of classes that is required to access the database. This set of classes is called view and if your data model is large then creating the view may
delay the web application response to the first request for a page. We can reduce this response time by creating view at compile time by using T4 template or EdmGen.exe command-line tool.
Avoid fetching not required fields from the database. Suppose I have table of Customer with 20 fields and I am interested only in three fields - CustomerID, Name, Address then fetch only these three fields instead of fetching all the fields of the Customer
12. }). ToList ();
In linq we have Var, IEnumerable, IQueryable, IList type collection for data manipulation. Each collection has its importance and performance impact on the query, so beware of using all these collection for data manipulation. For learning difference among
all these collection refer my articles IEnumerable VS IQueryable, IEnumerable VS IList and Var
Make a query to compiled query if it is frequently used to fetch records from the database. This query is slow in first time but after that it boost the performance significantly. We use Compile method of CompiledQuery class for making compiled query.
Suppose you required to retrieve customers details again and again based on city then make this query to compiled query like as
10.(ctx, city) =>from customer in ctx.Customers
11.where customer.City == city
In above query we are passing the string parameter city for filtering the records. For more about anonymous method.
When we are binding data to grid or doing paging, retrieve only required no of records to improve performance. This can achieved by using Take,While and Skip methods.
In LINQ, we use contains method for checking existence. It is converted to "WHERE IN" in SQL which cause performance degrades.
Views degrade the LINQ query performance costly. These are slow in performance and impact the performance greatly. So avoid using views in LINQ to Entities.
If you want to debug and optimize your query then LINQ Pad is a great tool for this purpose. I am a big fan of LINQ Pad. It is very useful for query construction, debugging and optimization.
Dump method of LINQ Pad give the result of above query in the result window.
Dec 19, 2016 01:52 PM|jayakumarvinayagam|LINK
In addition to that, got some cool stuff from https://msdn.microsoft.com/en-us/data/hh949853.