Hi All,
I am unable to convert following query join into sql query. Please help.
var result = (from sc in _context.ScenarioTags
join scn in _context.Scenarios on sc.ScenarioId equals scn.ScenarioId into scn_join
from scn in scn_join.DefaultIfEmpty()
join soc in _context.Scenario_Contacts on sc.ScenarioId equals soc.ScenarioId into soc_join
from soc in soc_join.DefaultIfEmpty()
join tl in _context.Tools on scn.ToolId equals tl.ToolId
join st in _context.ServicesTools on tl.ToolId equals st.ToolId
join s in _context.Services on st.ServiceId equals s.ServiceId
where sc.CreatedById == profileId || soc.ProfileId == profileId && scn.IsDeleted == false && scn.IsScenarioSaved == true && tl.IsActive == true && s.IsActive == true && resultListActiveContractUser.Contains(s.Code)
select new ScenarioTagsList
{
Tag = sc.Tag,
TagCount =
(from t in _context.ScenarioTags
join Scenarios in _context.Scenarios on t.ScenarioId equals Scenarios.ScenarioId into Scenarios_join
from Scenarios in Scenarios_join.DefaultIfEmpty()
join soc1 in _context.Scenario_Contacts on sc.ScenarioId equals soc.ScenarioId into soc_join1
from soc1 in soc_join1.DefaultIfEmpty()
join t2 in _context.Tools on Scenarios.ToolId equals t2.ToolId
join st1 in _context.ServicesTools on t2.ToolId equals st1.ToolId
join s1 in _context.Services on st1.ServiceId equals s1.ServiceId
where t.Tag == sc.Tag && Scenarios.IsDeleted == false && t2.IsActive == true && s1.IsActive == true && resultListActiveContractUser.Contains(s1.Code)
//t.Tag == sc.Tag && Scenarios.IsDeleted == false && t.CreatedById == profileId && t2.IsActive == true && s1.IsActive == true && resultListActiveContractUser.Contains(s1.Code)
select new
{
t.TagId
}).Distinct().Count(p => p.TagId != null)
}).Distinct().ToList();