Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Use Inner Join in FromSql Entity Framework Core method

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();
like image 662
Jourmand Avatar asked Oct 17 '22 18:10

Jourmand


1 Answers

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
like image 87
PSK Avatar answered Oct 21 '22 04:10

PSK