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();
Your Id
column appears in both the tables, Phones
and 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With