EF Core Query multiple tables with condition between

Question

I have 2 tables with the following structure:

User

UserId   |   Age
----------------
     1       15
     2       36
     3       25

DictAges

Title   |   FromAge   |   ToAge
-------------------------------
     1        0             20
     2        20            35
     3        35            100

I created the neccessary models for the DbContext, called UserModel and DictAgesModel with the exact same properties shown at the table structure. I would like to query a user with its id, and join title based on the user's age. Here's the MySQL code I used before:

SELECT User.UserId, DictAges.Title 
FROM User, DictAges 
WHERE User.UserId = :id 
  AND User.Age BETWEEN DictAges.FromAge AND DictAges.ToAge

Note, that I don't have any navigation property added to any of those models (should I add any??)

How could I translate a query like this to entity framework core query?

Answer 1

Hence you don't have navigational properties,you can do as shown below.

Query Based :

from p in ctx.User  
join q in ctx.DictAges on p.UserId equals q.Title
where p.UserId == :id AND p.Age BETWEEN q.FromAge AND q.ToAge
select new {UserId = p.UserId, Title = q.Title };

Method Based :

ctx.User.Join(ctx.DictAges, 
     p => p.UserId,
     q => q.Title, 
     (p, q) => new { User = p, DictAges = q })
.Where(s => s.User.UserId == :id && (s.DictAges.FromAge <= s.User.Age  && s.User.Age <=  s.DictAges.ToAge) )
.Select(ss => new { UserId = ss.User.UserId, Title = ss.DictAges.Title});

Note : Hence you don't have Navigation properties,method based syntax is very complex.In other words there are a readability issues. B'cos of that I would like Query based syntax on these situations.

Update :

You can learn about Navigational properties using this article : Relationships