where after select using linq-CodePudding

I would like to add where condition after my select but it wont let me. compiler complains and says a antonymous type for c inside the Where as it doesnt know if its clients or clientLogin

here is my code:

                    clientsLogin = (from c in db.Clients
                    join cl in db.clientLogin
                        on c.id equals cl.clientid
                    where c.active == true && cl.is_active == true
                    select new { cl, c }).ToList().
Where( c =>  DateTime.UtcNow < c.createdon.Value.AddHours( 48) && DateTime.UtcNow > c.createdon.Value.AddHours( 24)).ToList();

CodePudding user response：

Apparently you have an enumerable sequence of Clients, where every Client has a DateTime property Client.CreatedOn. Furthermore every Client has a primary key Id. A Client also has a Boolean property Client.Active.

You also have an enumerable sequence of ClientLogins, where every ClientLogin has a foreign key to the Client in Client.ClientId. A ClientLogin has a Boolean property ClientLogin.IsActive.

You have the current time. From this you can create a start time and a stop time. You want all Active Clients that were Created between StartTime and StopTime, each Client with its zero or more Active ClientLogins.

Well, in fact you didn't ask for this, you wanted only combinations of [Client, ClientLogin].

The difference is that in the first Solution if Client [32] has 2000 Logins, you will only send the data of Client [32] once. The second solution will send the data of Client [32] 2000 times, the same Client data each with a different ClientLogin.

It's up to you to decide whether you want the "Clients with their Logins", or the combinations [Client, ClientLogin]

Clients with their Logins

DateTime now = DateTime.UtcNow;
DateTime startTime = now.AddHours(...);
DateTime stopTime = now.AddHours(...);      // something with your 24 and 48

var activeClientsWithTheirActiveLogins = dbContext.Clients.
    .Where(client => client.Active
                  && startTime <= client.CreatedOn
                  && client.CreatedOn <= stopTime)

I'm not sure about your condition, whether it should be inside or outside your StartTime and StopTime, but hey, if you wanted a complete answer, next time give us a requirement in words. I guess you'll get the gist. By the way, did you notice that because of this, I'll calculate now and the time limits only once?

Continuing the LINQ: GroupJoin with Active ClientLogins

    .GroupJoin(dbContext.ClientLogins.Where(login => login.IsActive),
        client => client.Id,        // from every Client take the primary key
        login => login.ClientId,    // from every ClientLogin take the foreign key

        // parameter resultSelector: for every Clients, each with its zero or more ClientLogins
        // make one new:
        (client, loginsOfThisClient) => new
        {
            // Select only the properties of the Client that you plan to use
            Id = client.Id,
            Name = client.Name,
            ...

            // do not select, you already know that it is true
            // Active = client.Active,

            Logins = loginsOfThisClient.Select(login => new
            {
                // Select only the properties of the ClientLogin that you plan to use
                Id = login.Id,
                ...

                // do not select the foreign key, you already know the value
                // ClientId = login.ClientId,
            })
            .ToList(),
        });

Database management systems are extremely optimized for selecting data. One of the slower parts of your queries is the transport of data from the DBMS to your local process, hence it is wise to limit the transported data as much as possible. Only select properties that you plan to use.

Query [Client, Login] combinations

Your query will be similar. Instead of a GroupJoin use a Join:

var result = dbContext.Clients.
    .Where(client => client.Active
                  && startTime <= client.CreatedOn
                  && client.CreatedOn <= stopTime)
    .Join(dbContext.ClientLogins.Where(login => login.IsActive),
        client => client.Id,        // from every Client take the primary key
        login => login.ClientId,    // from every ClientLogin take the foreign key

        // Parameter resultSelector: for every [Client, Login] combination,
        // make one new
        (client, login) => new
        {
            Client = new
            {
                Id = client.Id,
                Name = client.Name,
                ...
            },
            Login = new
            {
                Id = login.Id,
                ...
            },
        });

There is a difference!

Apart from the fact that GroupJoin will be more efficient, there is a difference in data that you get. The GroupJoin will also return you "Clients without active ClientLogins". The Join won't have them.

If you don't want Clients without Logins, add an extra Where:

.Where(client => client.Logins.Any());

CodePudding user response：

ended up doing the following:

var clientsLoginAfter1Day = (from c in db.Clients
                            join cl in db.clientLogin
                                on c.id equals cl.clientid
                            where c.active == true && cl.is_active == true
                            select new { cl, c.createdon })
                        .ToList()
                        .Where(c => c.createdon >=  DateTime.UtcNow.AddHours(-48))
                        .Where(c => c.createdon < DateTime.UtcNow.AddHours(-24))
                        .ToList();