[Solved] summing values of a column based on id [closed]

by


First, I created a single table database to mimic the data structure:

static void CreateAndSeedDatabase()
{
    Context context = new Context();
    TimeTracker entry1 = new TimeTracker() { UserId = 1, LoginTime = new DateTime(2000, 1, 1, 0, 0, 0), LogoutTime = new DateTime(2000, 1, 1, 1, 0, 0) };
    TimeTracker entry2 = new TimeTracker() { UserId = 1, LoginTime = new DateTime(2000, 1, 1, 1, 0, 0), LogoutTime = new DateTime(2000, 1, 1, 2, 0, 0) };

    TimeTracker entry3 = new TimeTracker() { UserId = 1, LoginTime = new DateTime(2000, 1, 2, 0, 0, 0), LogoutTime = new DateTime(2000, 1, 2, 2, 0, 0) };

    TimeTracker entry4 = new TimeTracker() { UserId = 2, LoginTime = new DateTime(2000, 1, 1, 0, 0, 0), LogoutTime = new DateTime(2000, 1, 1, 1, 0, 0) };

    List<TimeTracker> entryList = new List<TimeTracker>() { entry1, entry2, entry3, entry4 };
    context.TimeTrackers.AddRange(entryList);
    context.SaveChanges();
}

class Context : DbContext
{
    public Context()
    {
        Database.SetInitializer(new CreateDatabaseIfNotExists<Context>());
        Database.Initialize(true);
    }

    public DbSet<TimeTracker> TimeTrackers { get; set; } 
}

public class TimeTracker
{
    public int TimeTrackerId { get; set; }
    public int UserId { get; set; }
    public DateTime LoginTime { get; set; }
    public DateTime LogoutTime { get; set; }
}

The following picture shows the results within SQL Server:

Data Setup

Next, I created the following stored procedure:

CREATE PROCEDURE GroupByUserAndDate
AS
BEGIN
    SELECT TimeTracker.UserId, CAST(TimeTracker.LogoutTime AS date) AS LogoutDate,  SUM(DATEDIFF(SECOND, TimeTracker.LoginTime, TimeTracker.LogoutTime)) AS TimeSum
    FROM [ef_6_code_first_database_table_not_create.Context].[dbo].[TimeTrackers] AS TimeTracker
    GROUP BY TimeTracker.UserId, CAST(TimeTracker.LogoutTime AS date)
END

This produces the following results:

Stored Procedure Results

Lastly, I call the stored procedure using EntityFramework:

static void ExecuteStoredProcedure()
{
    Context context = new Context();
    QueryResult[] results = context.Database.SqlQuery<QueryResult>("dbo.GroupByUserAndDate").ToArray();
}

public class QueryResult
{
    public int UserId { get; set; }
    public DateTime LogoutDate { get; set; }
    public int TimeSum { get; set; }
}

solved summing values of a column based on id [closed]