Myujth

Models :

MaterialTemplate
 int id,
 MaterialType materialType,
 int length,
 int width,
 int complexity


Material
 int id,
 MaterialType materialType,
 int length,
 int width,
 int complexity,
 int saleType

I have
List<MaterialTemplate> requiredMaterialTemplates;

I would like to check if each required material template is available in Material table with 2x availability, where '2x' is configurable.

I can achieve this by looping over requiredMaterialTemplates and checking one by one with linq query but is there any way to check all the requiredMaterialTemplates at once with linq(1 query to SQL).

Also, how can we get number of materials required for each material template if provided availability is 2x without looping every template in requiredMaterialTemplates.

Example:

Required materials with Availability value as 2x

MaterialTemplateId:1,MaterialType:"Bronze",Complexity:"Solid"
MaterialTemplateId:2,MaterialType:"Silver",Complexity:"Solid",Length:10

Materials in db

1,"Bronze",10,5,"Solid","Limited"
2,"Bronze",20,6,"Solid","Limited"
3,"Silver",10,5,"Solid","Limited"
4,"Copper",10,5,"Solid","Limited"

Result should be

MaterialTemplateId, Required Count
1,0
2,1

I think you can use the group by to get all the materialType you have twice or more.
Something like this:

from m in Material
group m by m.MaterialType into grp
where grp.Count() > 1
select grp.Key

If I understood right what you need:

from material in availableMaterials
join template in requiredMaterialTemplates 
 on new material.materialType, material.length, material.width, material.complexity 
 equals new template.materialType, template.length, template.width, template.complexity 
where material.quantity < 2
select material

Here I assume you have List<Material> availableMaterials.