我们如何在数据库获取的数据上执行组件操作

Question

string date = DateTime.Today.Date.ToShortDateString();
var grouped = from a in db.Logs
    group a by a.email
    into g
    select new
    {
        intime = (from x in db.Logs where x.date == date && x.email == "" select x.login).Min();
        outime = (from x in db.Logs where x.date == date && x.email == "" select x.login).Min()
    };
return View();

我正在使用具有email和login_time的表

我需要根据email进行分组，从中我需要在当前日期获取特定email_id的最小login_time。我使用min函数查找第一个登录。我是Linq的新手我的桌子有字段1.Login2.logout3. email4. username

每当用户登录时，表都会填充登录时间，注销时间，电子邮件，用户名。=>我需要根据当前日期的电子邮件对这些详细信息进行分类。因此，该管理查看页面应仅具有当前日期的特定电子邮件的第一个登录和最后登录。

Answer 1

您写的：

我需要根据电子邮件对它们进行分组，从中我需要在当前日期获取特定email_id的最小login_time。

为此，我将使用ElementsElector的Queryable.groupby。

DateTime selectionDate = DateTime.UtcNow.Date;  // or any other date you want to use

var result = db.Logs
    // Keep only logs that have an e-mail on the selection date:
    .Where(log => log.LogInTime == selectionDate)
    // Group all remaining logs into logs with same email
    .GroupBy(log => log.email,
    // element selector: I only need the LoginTimes of the Logs
    log => log.LoginTime,
    // result selector: take the email, and all logInTimes of logs 
    // with this email to make a new object
    (email, logInTimesForThisEmail) =>  new
    {
        Email = email, // only if desired in your end result
        // Order the loginTimes and keep the first,
        // which is the min login time of this email on this date
        MinLoginTimeOnDate = logInTimesForThisEmail
            .OrderBy(logInTime => loginTime)
            .FirstOrDefault(),

您的示例代码显示您还希望在选定的日期最大登录时间。使用单独的选择，因此您必须仅订购一次元素：

(email, logInTimesForThisEmail) =>  new
{
    Email = email, // only if desired in your end result
    LogInTimes = logInTimesForThisEmail
        .OrderBy(loginTime => loginTime);
})
.Select(groupResult => new
{
    Email = groupResult.Email,
    MinTime = groupResult.LogInTimes.FirstOrDefault(),
    MaxTime = groupResult.LogInTimes.LastOrDefault(),
});

如果您需要更多的字段，而不仅仅是电子邮件和登录时间，请更改组的ElementSelector参数，以便它还包含这些其他字段。

Answer 2

string date = DateTime.Today.Date.ToShortDateString();
var grouped = from a in db.Logs.ToList()
    where a.date == date
    group a by a.email
    into g
    let ordered = g.OrderBy(x => x.date).ToList()
    let firstLogin = ordered.First()
    let lastLogin = ordered.Last()
    select new
    {
        first_login_time = firstLogin.login_time,
        first_login = firstLogin.login,
        last_login_time = lastLogin.login_time,
        last_login = lastLogin.login        
    };

更新

管理视图页面应仅具有第一个登录和最后注销 当前日期的特定电子邮件。

string date = DateTime.Today.Date.ToShortDateString();
var grouped = from a in db.Logs.ToList()
    where a.login.Date == date
    group a by a.email
    into g
    let firstLogin = g.OrderBy(x => x.login).First() // order by login time and get first
    let lastLogout = g.OrderBy(x => x.logout).Last() // order by lotgout time and get last
    select new
    {
        email: g.Key,
        first_login = firstLogin.login, // first login
        last_logout = lastLogin.logout // last logout
    };

我希望您的login和logout字段具有datetime类型。您确实会更清楚地提出问题。当我要求您获取表格架上时，我以为您会让我这样倾斜：

CREATE TABLE [dbo].[Logs](
    [username] [nvarchar](4000) NOT NULL,
    [email] [nvarchar](4000) NOT NULL,
    [login] [datetime] NULL,
    [logout] [datetime] NULL
)

或类声明

public class Log {
  public string email {get; set; }
  public string username {get; set; }
  public DateTime login {get; set; }
  public DateTime logout {get; set; }
}

您可以学习如何提出问题

更新2

假设我有3个不同的电子邮件。如果他们访问了我的申请 记录了登录时间和注销时间。

Table[Login_details]
id  employee    date        login   logout  email
1   ShobaBTM    2019-03-18  16:12   16:12   [e-mail]
2   neymarjr    2019-03-18  16:22   16:22   [e-mail]
3   Cristiano   2019-03-18  16:23   16:23   [e-mail]
4   neymarjr    2019-03-18  16:25   16:25   [e-mail]
5   neymarjr    2019-03-18  16:30   16:32   [e-mail]
6   neymarjr    2019-03-18  16:42   16:45   [e-mail]

在管理员视图中，我应该有这个

1   ShobaBTM    2019-03-18  16:12   16:12   
2   Cristiano   2019-03-18  16:23   16:23
3   neymarjr    2019-03-18  16:25   16:45   

好吧，尝试以下操作：

string date = DateTime.Today.Date.ToShortDateString();
var grouped = from a in db.Logs.ToList()
    where a.date == date
    group a by new { a.employee, a.date }
    into g
    let firstLogin = g.OrderBy(x => TimeSpan.ParseExact(x.login, "hh\:mm")).First() // order by login time and get first
    let lastLogout = g.OrderBy(x => TimeSpan.ParseExact(x.logout, "hh\:mm")).Last() // order by logout time and get last
    select new
    {
        employee = g.Key.employee,
        date = g.Key.date,
        first_login = firstLogin.login, // first login
        last_logout = lastLogin.logout // last logout
    };

该代码有效吗？如果没有 - 到底发生了什么？