Is it possible to use
Inner Join in the
FromSql method of Entity Framework core?
I used, but I got this error:
SqlException: The column 'Id' was specified multiple times for 'c'.
This is my code:
return DbContext.Contacts .FromSql<Contact>("Select * From Contacts Inner Join Phones on Phones.ContactId = Contacts.Id Where Contacts.Id <= 2 And Phones.PhoneNumber='01234567890'") .Include(o => o.RegisteredByUser) .AsNoTracking() .ToListAsync();
Id column appears in both the tables,
Contacts. Instead of putting
*, you better choose the fields required in your query like following.
Please note, you need to specify which Id you want, If you want both, in that case you can use alias names like following.
Select Phones.Id as PhoneId, Contacts.Id as ContactsId, ....
Your final query should look like following query.
Select Contacts.Id, ... From Contacts Inner Join Phones on Phones.ContactId = Contacts.Id Where Contacts.Id <= 2 And Phones.PhoneNumber='01234567890