I am unable to convert the following SQL query to .Net Entity framework

Question

SELECT * 
FROM HCMTemplates 
INNER JOIN Status ON HCMTemplates.TemplateStatus=Status.Id 
INNER JOIN Country ON HCMTemplates.AssignToCountry=Country.code
Where HCMTemplates.TemplateName like '%ABC%'

I am unable write this query in format of Entity FrameWork

Answer 1

Assuming the tables listed have relationships like

public class HCMTemplates
{
   //properties
   public ICollection<Status> Status {get; set;}
   public ICollection<Country> Country {get; set;}
}

public class Status
{
   //properties
   public HCMTemplates HCMTemplates {get; set;}
}

public class Country
{
   //properties
   public HCMTemplates HCMTemplates {get; set;}
}

you could use

context.HCMTemplates.Include(x => x.Status).Include(x => x.Country).Where(x => x.TemplateName.Contains("ABC")).ToList();

if no relationship is establish

 context.HCMTemplates
    .Join(
        dbContext.Status,
        hCMTemplates=> hCMTemplates.TemplateStatus,
        status=> status.Id,
        (hCMTemplates, status) => new { hCMTemplates, status}
    )
    .Join(
        dbContext.Country,
        joinedTable => joinedTable.hCMTemplates.AssignToCountry,
        country=> country.code,
        (joinedTable , country) => new { joinedTable , country}
    )
    .Where(x => x.joinedTable.hCMTemplates.TemplateName.Contains("ABC")).ToList()

not sure with the Where clause, but mostly that is the code

Answer 2

The where statement actually doesn't really work as you would expect for entity framework. You cannot simply use a where after an include and expect it to work. Check out this article: https://entityframework.net/include-with-where-clause

Did you try this? If you did, can you show us your C# code?