Use Inner Join in FromSql Entity Framework Core method

asp.net-core entity-framework entity-framework-core

Question

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();
1
4
5/13/2018 6:46:58 AM

Accepted Answer

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
2
5/13/2018 6:52:49 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow