CODEWITHSUNDEEP
c#entity-frameworklinqjoin
Nikola
Nikola

Reputation: 2132

Filter dataset on JOIN based on other table value

I am using Entity Framework. I have 2 tables: Unit and Message. They look like that:

public class Unit
{
    int id;
    DateTime date;
}

public class Message
{
    int id;
    int unitId;
    DateTime date;
}

I want to join every Unit for every Message.

I do something like that:

DateTime from = DateTime.Now;

 db.Unit.Join(
    db.Message.Where(m => m.date >= from),
    u => u.id,
    s => s.unitId,
    (u, s) => new 
    {
        MessageTime = s.date,
        UnitTime = u.date
    }
)
.AsEnumerable()
.Select(r => new 
    {
        MessageTime = MessageTime,
        UnitTime = UnitTime
    }
)
.ToList()

The Unit class contains date which I want to use when doing the join. I only want to take the messages from Message which has date >= unit.date. I know that I can do .Where(x => x.UnitTime >= x.MessageTime), but I do not want to make the join and filter after that.

Is it possible to filter based on the other table?

Upvotes: 1

Views: 81

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205599

LINQ supports only equijoins. In case you need different type of join, you can replace Join with SelectMany + Where - EF is smart enough to convert it to SQL INNER JOIN:

db.Unit.SelectMany(
    u => db.Message.Where(m => u.id == m.unitId && m.date >= u.date && m.date >= from),
    (u, m) => new
    {
        MessageTime = m.date,
        UnitTime = u.date
    }
)

Upvotes: 2

Related Questions