How to use LINQ to search and item from the database

Question

This is MySQL

SELECT *
FROM Cities as a
inner join Countries as b
on a.CountryCode = b.Code
where b.Name like CountryName

How can I wrote this into LINQ I only manage to write until this part

from country in db.Set<Country>()
join city in db.Set<City>() on country.CountryCode equals city.CountryCode

This is just the inner join How can I continue in order to get the output for the CountryName

You need to specify if this is LINQ to SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x? Also, what does `CountryName` look like? LINQ to EF has some translations for `like` involving `String.Contains` but not a general one. — NetMage, Jan 19 '21 at 19:05
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jan 19 '21 at 19:17
If the problem is in the `like` part, did you try [DbFunctions.Like](https://learn.microsoft.com/en-us/dotnet/api/system.data.entity.dbfunctions.like?view=entity-framework-6.2.0)? — Harald Coppoolse, Jan 20 '21 at 07:22
`from city in db.Set() join country in db.Set() on city.CountryCode equals country.Code where Country.Name.Contains(CountryName) select new { country, city }` — Aluan Haddad, Jan 22 '21 at 15:56

score 0 · Accepted Answer · answered Jan 19 '21 at 19:21

0

For Entity Framework 6.x, you would use

where DbFunctions.Like(country.name, CountryName)

For EF Core, you would use

where EF.Functions.Like(country.name, CountryName)

answered Jan 19 '21 at 19:21

NetMage

1 Answers1