My project has subqueries used inside nested Query.While using a subquery inside the inner query, variable declared in outer query becomes inaccesible to inner queries inside it.
Error found while debugging nested queries:
- Unknown column ‘Join3.appcategoryid’ in ‘field list’
- Unknown column ‘Extent1.taxonomyid’ in ‘where clause’
public static dynamic GetTaxonomies(int businessUnitId, int memberId, string businessUnitType)
{
using (var db = new ProtocolManagementDatabaseEntities())
{
var taxonomies = (from taxonomy in db.taxonomies
join businessUnitTaxonomy in db.businessunittaxonomies on taxonomy.taxonomyid equals businessUnitTaxonomy.taxonomyid
let TaxonomyID = taxonomy.taxonomyid
where businessUnitTaxonomy.businessunitid == businessUnitId
select new
{
TaxonomyId = taxonomy.taxonomyid,
TaxonomyName = taxonomy.taxonomyname,
preferenceValue = db.userpreferences.Where(x => x.entitycolumnvalue == TaxonomyID &&
x.memberid == memberId && x.entitytablename == "Taxonomy" && x.sequencenumber == 5)
.Select(x => x.isvisible).FirstOrDefault()
AppCategory = (from appCategory in db.appcategories
join appCategoryTaxonomy in db.appcategorytaxonomies on appCategory.appcategoryid equals appCategoryTaxonomy.appcategoryid
let AppCategoryID = appCategory.appcategoryid
where appCategory.parentappcategoryid == null &&
appCategory.appcategorylevel.Equals("AppCategoryName")
&& appCategoryTaxonomy.taxonomyid == TaxonomyID &&
appCategoryTaxonomy.businessunitid == businessUnitId
orderby appCategory.name ascending
select new
{
AppCategoryId = appCategory.appcategoryid,
AppCategoryName = appCategory.name,
AppCategoryDeletionEnable = db.appcategories.Where(x => x.parentappcategoryid == AppCategoryID)
.FirstOrDefault() != null ? true : false
SubCategory = from subCategory in db.appcategories
let SubCategoryID = db.appcategories.Where(x => x.appcategoryid == subCategory.appcategoryid).Select(x => x.appcategoryid).FirstOrDefault()
where subCategory.parentappcategoryid == AppCategoryID && subCategory.businessunitid == businessUnitId
orderby subCategory.name ascending
select new
{
AppCategoryId = subCategory.appcategoryid,
AppCategoryName = subCategory.name,
Description = subCategory.description,
IsGlobal = subCategory.isglobal,
IsEnable = subCategory.isenable,
ParentAppCategoryID = appCategory.parentappcategoryid,
SubCategoryDeleteEnable = db.protocolappcategories.Where(x => x.appcategoryid == SubCategoryID)
.FirstOrDefault() != null ? true : false,
AppCategoryLevel = subCategory.appcategorylevel,
IsExpand = false,
}
}).ToList()
}).ToList();
return taxonomies;
}
}