Think of me. A field was added to the list to make it easier to hint to the manager whether the company's product list has a status field, such as a change, so you can determine why this field was added.
First, let's look at how I wrote this hint state field first, add Contentstatus to the entity, and then add Contentstatus=product_maintain.count (C =>) directly to the Select Entity object in the LINQ statement. C.companyid = = Company.id && c.isdeleted = 0 && (c.auditstatus = = 0 | | C.auditstatus = 4) >0? "Product has Update": "". At this time I think should be added ternary operation, LINQ in turn SQL, produce too much, case,when, then statement, ternary operation increased judgment will affect query performance, so I removed, then run the View page, still very slow, not feeling much faster.
At this point, I thought of linqpad to see what kind of SQL statement the transformation generated. Use COUNT (condition) to generate the following SQL code:
Copy Code code as follows:
SELECT COUNT (*) as [value]
From (
SELECT
(case
When ([t1].[ CompanyID] = ([t0].[ ID])) and ([t1].[ IsDeleted] = @p0) and ([t1].[ Auditstatus] = @p1) OR ([t1].[ Auditstatus] = @p2)) THEN 1
When not ([t1].[ CompanyID] = ([t0].[ ID])) and ([t1].[ IsDeleted] = @p0) and ([t1].[ Auditstatus] = @p1) OR ([t1].[ Auditstatus] = @p2)) THEN 0
ELSE NULL
End) as [value]
From [Company_product_maintain] as [T1]
) as [T2]
WHERE [T2]. [Value] = 1
At this point I found a very simple count of the SQL statement, the LINQ transformation became so complex, I ran this code directly in SQL Server, found that the query is still very slow, so I directly to the Contentstatus=product_maintain.where (C => C.companyid = = company.id && c.isdeleted = 0 && (c.auditstatus = 0 | | C.auditstatus = = 4)). Count () generates the SQL statement as:
Code
Copy Code code as follows:
SELECT COUNT (*) as [value]
From [Gassns_company_equipment_maintain] as [T1]
WHERE ([t1].[ CompanyID] = ([t0].[ ID])) and ([t1].[ IsDeleted] = @p0) and ([t1].[ Auditstatus] = @p1) OR ([t1].[ Auditstatus] = @p2))
Find the running speed that is fast an order of magnitude ah!
Background list query results are greatly enhanced by the picture as evidence ( Statement:The following diagram is a screenshot of the project, not a simple one-table query, but also the user table, detailed table, and so the number is very large)：
Figure 1 is the count result, in 35 seconds, wow!
Figure 2 is where (condition). Count () results, the same data in only 4 seconds, 10 times times worse!
Then in order to take the value I still add ternary operation, Contentstatus=product_maintain.where (C => C.companyid = = company.id && c.isdeleted = 0 & amp;& (C.auditstatus = = 0 | | C.auditstatus = = 4)). Count () >0? "Product has Update": "". The results are as follows:
Really is the difference between count () and Where (), is it possible to have such a big gap? So I wrote it alone.
Product_maintain.where (C => c.isdeleted = 0 && (c.auditstatus = = 0 | | C.auditstatus = = 4)). Count () and
Product_maintain.count (C => c.isdeleted = 0 && (c.auditstatus = = 0 | | C.auditstatus = 4))
Found that the speed is similar, the generated code is the same.
Copy Code code as follows:
SELECT COUNT (*) as [value]
From [Gassns_company_equipment_maintain] as [t0]
WHERE ([t0].[ IsDeleted] = @p0) and ([t0].[ Auditstatus] = @p1) OR ([t0].[ Auditstatus] = @p2))
It was me. Count (condition) and where (condition) are used when the number of a table is taken in a select and a variable from the previous from is applied in the condition. Count () produces the code differently, and the query speed can differ in order of magnitude.
Code
Copy Code code as follows:
Low efficiency version:
From Company by company
Select New
{
Contacter = V.contacterid,
Count = Product_maintain.count (C => C.companyid = = Company.id &&c.isdeleted = 0 && (c.auditstatus = 0 || C.auditstatus = 4))
}
And
Copy Code code as follows:
High Efficiency version:
From Company by company
Select New
{
Contacter = V.contacterid,
Count = Product_maintain.where (C =>c.companyid = = company.id && c.isdeleted = 0 && (c.auditstatus = 0 || C.auditstatus = = 4)). Count ()
}
Otherwise, Count () and where (). The SQL statements generated by Count () are the same and have the same efficiency.
Summing up to this, I hope you reader to pay attention after! I have been in the park for two years, the first hair on the home page, please reader the generous enlighten!
Thank you for Reader's advice, the statement of the above query map are linqpad query results screenshots. For 4 seconds or so for the LINQPad query time, the LINQ generation SQL statement executes in SQL Server less than 1 seconds, the following screenshot explains:
