0

I kinda need a brainstorm here..

Here's my scenario:

public class UserSystem
{
    public virtual User User { get; set; }
    public virtual System System { get; set; }
}

public class User
{
    public User() { }
    public virtual int UsrId { get; set; }
}

public class System
{
    public System() { }
    public virtual decimal SistId { get; set; }
    public virtual IList<Perf> SystPerf { get; set; }
}

public class Perf
{
    public Perf() { }
    public virtual int PerfId { get; set; }
    public virtual System System { get; set; }
    public virtual string Perf_Adm_Portal { get; set; }
}

I need to get all the users that has Perf_Adm_Portal == "S". I know its kinda simple but I am doing something wrongly...

I tryed this:

var list = session.Query<UserSystem>()
   .Fetch(x => x.User)
   .Fetch(x => x.System)
        .ThenFetch(x => x.SystPerf)
   .Where(x => x.System.SistId == someId)
   //.Where(x => x.Sistema.SystPerf.Where(x => x.Perf_Adm_Portal == "S"))
   .ToList<USerSystem>();

that commented line its just what I want but it doesn't work... doesn't even compile. So this query returns me all users (including those who has the flag Perf_Adm_Portal != "S") and then I just treat them in memory... but its taking so long to execute this query and I know that there is a better solution... can you guys help me? Regards

**Edit

Nevermind, guys... I just realize that I have a third table (UserPerf). And each perf has its own System.

So I retrieve all admins of a system like this:

var list = session.Query<UserPerf>()
                              .Where(up => up.Perf.Perf_Adm_Portal.ToLower().Equals("yes"))
                              .Where(up => up.Perf.System.SistId == sistId)
                              .Select(up => up.User)
                              .ToList<User>();

Sorry for the trouble...

@Radim Köhler, thanks for your time! But I think what I was trying to do it's impossible without that third table below.

Regards,

Marllon Nasser
  • 390
  • 4
  • 22

1 Answers1

0

I would say, that this kind of query should work for you:

var inner = session.Query<UserSystem>()
    .Where(e => e.System.SystPerf.Any(p => p.Perf_Adm_Portal == "S"))
    .Select(e => e.User.UserId);

var query = session.Query<User>()
    .Where(i => inner.Contains(u.userId));


var list = query
    // .Skip(y).Take(x) // paging
    .ToList();
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335