Optimize the join operation by using linq to SQL

Initial time: frequent timeout
---------------------------
Var innerJoinQuery = from ca in context. caccey
Join cls in context. cclass on ca. caccey_cclassid equals cls. cclass_cclassID
Join stl in context. cstyle on ca. caccey_cstyleid equals stl. cstyle_cstyleID
Join loc in context. Custom_Captions on ca. caccey_location equals loc. Capt_Code
Join suloc in context. subloc on ca. caccey_sublocid equals suloc. subloc_sublocID
Where string. Compare (ca. caccey_Name, barCode, true) = 0
& String. Compare (loc. Capt_FamilyType, "Choices", true) = 0 & string. Compare (loc. Capt_Family, "lists_location", true) = 0
Select new Garnishry
{
Id = ca. caccey_cacceyID,
Name = ca. caccey_Name,
ItemNo = ca. caccey_itemno,
MaterialId = ca. caccey_cqualyid,
Stone = ca. caccey_stone,
ClassId = ca. caccey_cclassid,
StyleId = ca. caccey_cstyleid,
LocationId = ca. caccey_location,
Location = new StocktakingLocation
{
AreaCode = loc. Capt_Code,
AreaName = loc. Capt_CS
},
SubLocationId = ca. caccey_sublocid,
SubLocation = new SubLocation
{
Id = suloc. subloc_sublocID,
SubLocationName = suloc. subloc_Name.Trim ()
},
ItemName = ca. caccey_itemname
};

First optimization time: 1st times 11 ~ 15 s, 2nd times 3 ~ 4S, 3rd Times 1 s
----------------------
Var innerJoinQuery = from ca in context. caccey
Join cls in context. cclass on ca. caccey_cclassid equals cls. cclass_cclassID
Join stl in context. cstyle on ca. caccey_cstyleid equals stl. cstyle_cstyleID
Join loc in context. Custom_Captions on ca. caccey_location equals loc. Capt_Code
Join suloc in context. subloc on ca. caccey_sublocid equals suloc. subloc_sublocID into caGroup
From suLocItem in caGroup. DefaultIfEmpty ()
Where string. Compare (ca. caccey_Name, barCode, true) = 0
& String. Compare (loc. Capt_FamilyType, "Choices", true) = 0 & string. Compare (loc. Capt_Family, "lists_location", true) = 0
Select new Garnishry
{
Id = ca. caccey_cacceyID,
Name = ca. caccey_Name,
ItemNo = ca. caccey_itemno,
MaterialId = ca. caccey_cqualyid,
Stone = ca. caccey_stone,
ClassId = ca. caccey_cclassid,
Class = cls. cclass_Name,
StyleId = ca. caccey_cstyleid,
Style = stl. cstyle_Name,
LocationId = ca. caccey_location,
Location = new StocktakingLocation
{
AreaCode = loc. Capt_Code,
AreaName = loc. Capt_CS
},
SubLocationId = ca. caccey_sublocid,
SubLocation = ca. caccey_sublocid.HasValue? New SubLocation
{
Id = suLocItem. subloc_sublocID,
SubLocationName = suLocItem. subloc_Name.Trim ()
}: Null,
ItemName = ca. caccey_itemname
};

Second optimization time: 1st times 5 ~ 8 s, 2nd times 1 ~ 2 s, 3rd Times 1 s
---------------------------------

Var innerJoinQuery = from ca in context. caccey. Where (ca 2 => string. Compare (ca2.caccey _ Name, barCode, true) = 0)
Join cls in context. cclass on ca. caccey_cclassid equals cls. cclass_cclassID into caCls
From caCls1 in caCls. DefaultIfEmpty ()
Join stl in context. cstyle on ca. caccey_cstyleid equals stl. cstyle_cstyleID
Join loc in context. custom_Captions.Where (loc2 => string. compare (loc2.Capt _ FamilyType, "Choices", true) = 0 & string. compare (loc2.Capt _ Family, "lists_location", true) = 0)
On ca. caccey_location equals loc. Capt_Code
Join suloc in context. subloc on ca. caccey_sublocid equals suloc. subloc_sublocID into caGroup
From suLocItem in caGroup. DefaultIfEmpty ()
// Where string. Compare (ca. caccey_Name, barCode, true) = 0 &&
// String. Compare (loc. Capt_FamilyType, "Choices", true) = 0 & string. Compare (loc. Capt_Family, "lists_location", true) = 0
Select new Garnishry
{
Id = ca. caccey_cacceyID,
Name = ca. caccey_Name,
ItemNo = ca. caccey_itemno,
MaterialId = ca. caccey_cqualyid,
Stone = ca. caccey_stone,
ClassId = ca. caccey_cclassid,
Class = caCls1.cclass _ Name,
StyleId = ca. caccey_cstyleid,
Style = stl. cstyle_Name,
LocationId = ca. caccey_location,
Location = new StocktakingLocation
{
AreaCode = loc. Capt_Code,
AreaName = loc. Capt_CS
},
SubLocationId = ca. caccey_sublocid,
SubLocation = ca. caccey_sublocid.HasValue? New SubLocation
{
Id = suLocItem. subloc_sublocID,
SubLocationName = suLocItem. subloc_Name.Trim ()
}: Null,
ItemName = ca. caccey_itemname
};

Third optimization time: 1st 3 ~ 5S, 2nd times 1 s
-------------------------------

Garnishry g = (from t in context. caccey
Where string. Compare (t. caccey_Name, barCode, true) = 0
Select new Garnishry ()
{
Id = t. caccey_cacceyID,
Name = t. caccey_Name,
ItemNo = t. caccey_itemno,
MaterialId = t. caccey_cqualyid,
Stone = t. caccey_stone,
ClassId = t. caccey_cclassid,
StyleId = t. caccey_cstyleid,
LocationId = t. caccey_location,
SubLocationId = t. caccey_sublocid,
ItemName = t. caccey_itemname
}). FirstOrDefault ();

If (g! = Null)
{
Var innerJoinQuery = from cls in context. cclass. Where (cls2 => cls2.cclass _ cclassID = g. ClassId)
Join stl in context. cstyle on g. StyleId equals stl. cstyle_cstyleID
Join loc in context. custom_Captions.Where (loc2 => string. compare (loc2.Capt _ FamilyType, "Choices", true) = 0 & string. compare (loc2.Capt _ Family, "lists_location", true) = 0)
On g. LocationId equals loc. Capt_Code
Join suloc in context. subloc on g. SubLocationId equals suloc. subloc_sublocID into caGroup
From suLocItem in caGroup. DefaultIfEmpty ()
Select new Garnishry
{
Id = g. Id,
Name = g. Name,
ItemNo = g. ItemNo,
MaterialId = g. MaterialId,
Stone = g. Stone,

ClassId = g. ClassId,
Class = cls. cclass_Name,

StyleId = g. StyleId,
Style = stl. cstyle_Name,

LocationId = g. LocationId,
Location = new StocktakingLocation
{
AreaCode = loc. Capt_Code,
AreaName = loc. Capt_CS
},

SubLocationId = g. SubLocationId,
SubLocation = g. SubLocationId. HasValue? New SubLocation
{
Id = suLocItem. subloc_sublocID,
SubLocationName = suLocItem. subloc_Name.Trim ()
}: Null,
ItemName = g. ItemName
};
G = innerJoinQuery. FirstOrDefault ();
}
Fourth optimization time: 1st times per second
---------------
Create indexes for related fields in the database. Especially data tables with large data volumes.

Conclusion:
-----------------
It is better to directly create an index for the database to optimize the previous optimizations.