在ASP.NET MVC中如何计算聊天工单的平均处理时长？

Hey there! Let's walk through how to solve your two key needs: figuring out the duration of individual chat sessions and calculating the average handling time for completed chats in your ASP.NET MVC + Entity Framework app.

1. Calculating Individual Chat Duration #

First, since you need the time between ChatStartDateTime (required, non-null) and ChatEndDateTime (nullable, set only when the chat is marked complete), you can add a computed property to your Chat model. This property won't be stored in the database (we'll mark it with [NotMapped]), but it will calculate the duration on the fly.

Update your Chat model like this:

using System.ComponentModel.DataAnnotations.Schema; // Required for [NotMapped]

public class Chat {
    [Key]
    public int ChatId { get; set; }
    [Required]
    public string CustName { get; set; }
    public string Query { get; set; }
    public string Resolution { get; set; }
    [Required]
    public DateTime ChatStartDateTime { get; set; }
    public DateTime? ChatCreateDateTime { get; set; }
    public DateTime? ChatEndDateTime { get; set; }
    public int Id { get; set; }
    public string Username { get; set; }
    public string FirstName { get; set; }
    public string Email { get; set; }
    [ForeignKey("Id")]
    public virtual User User { get; set; }

    // Computed property for chat duration
    [NotMapped]
    public TimeSpan? ChatDuration
    {
        get
        {
            // Only calculate if chat has ended
            if (ChatEndDateTime.HasValue)
            {
                return ChatEndDateTime.Value - ChatStartDateTime;
            }
            // Optional: Uncomment below to show elapsed time for active chats
            // return DateTime.Now - ChatStartDateTime;
            return null; // Return null for incomplete chats
        }
    }
}

Using This in Your View #

In your MyChats.cshtml view, display the duration with null safety:

@foreach (var chat in Model)
{
    <tr>
        <td>@chat.Email</td>
        <td>@chat.ChatStartDateTime.ToString("yyyy-MM-dd HH:mm")</td>
        <td>@(chat.ChatEndDateTime?.ToString("yyyy-MM-dd HH:mm") ?? "In Progress")</td>
        <td>@(chat.ChatDuration?.TotalMinutes.ToString("F2") ?? "N/A") minutes</td>
        <!-- Add other columns as needed -->
    </tr>
}

2. Calculating Average Handling Time #

To get the average time spent on completed chats, you'll filter out incomplete sessions, sum their total duration, and divide by the count of completed chats. Here are two approaches depending on your dataset size:

Option 1: Calculate in Memory (Small Datasets) #

If you're working with a small number of chats, fetch the data first and compute the average in your controller:

public ActionResult MyChats() {
    using (Db db = new Db()) {
        var uName = User.Identity.Name;
        
        // Your existing today's ticket count logic
        ViewBag.myTodayTicket = db.Chats
            .Where(x => System.Data.Entity.DbFunctions.TruncateTime(x.ChatCreateDateTime) == DateTime.Today && x.Username == uName)
            .Count();

        // Get all user's chats
        var userChats = db.Chats.Where(x => x.Username == uName).ToList();

        // Calculate average for completed chats
        var completedChats = userChats.Where(c => c.ChatEndDateTime.HasValue).ToList();
        TimeSpan averageDuration = TimeSpan.Zero;
        
        if (completedChats.Any())
        {
            var totalMilliseconds = completedChats.Sum(c => (c.ChatEndDateTime.Value - c.ChatStartDateTime).TotalMilliseconds);
            averageDuration = TimeSpan.FromMilliseconds(totalMilliseconds / completedChats.Count);
        }

        ViewBag.AverageHandlingTime = averageDuration;
        return View(userChats);
    }
}

Option 2: Calculate in Database (Large Datasets) #

For larger datasets, let the database handle the calculation to avoid pulling all data into memory. Use Entity Framework's DbFunctions to compute duration directly in SQL:

public ActionResult MyChats() {
    using (Db db = new Db()) {
        var uName = User.Identity.Name;
        
        // Existing today's ticket count
        ViewBag.myTodayTicket = db.Chats
            .Where(x => System.Data.Entity.DbFunctions.TruncateTime(x.ChatCreateDateTime) == DateTime.Today && x.Username == uName)
            .Count();

        // Calculate total minutes and count of completed chats in the database
        var totalMinutes = db.Chats
            .Where(x => x.Username == uName && x.ChatEndDateTime.HasValue)
            .Sum(x => System.Data.Entity.DbFunctions.DiffMinutes(x.ChatStartDateTime, x.ChatEndDateTime));
        
        var completedChatCount = db.Chats
            .Where(x => x.Username == uName && x.ChatEndDateTime.HasValue)
            .Count();

        // Compute average (handle division by zero)
        double averageMinutes = completedChatCount > 0 ? (double)totalMinutes / completedChatCount : 0;
        ViewBag.AverageHandlingTime = TimeSpan.FromMinutes(averageMinutes);

        var userChats = db.Chats.Where(x => x.Username == uName).ToList();
        return View(userChats);
    }
}

Displaying the Average in Your View #

Add this snippet to your view to show the average handling time:

<div class="alert alert-info">
    Average Handling Time: @ViewBag.AverageHandlingTime.ToString(@"hh\:mm\:ss")
</div>

Quick Tips #

• Time Zones: Store all DateTime values in UTC (use DateTime.UtcNow when setting ChatStartDateTime and ChatEndDateTime) to avoid timezone-related discrepancies.
• Null Safety: Always check if ChatEndDateTime has a value before calculating duration to prevent null reference exceptions.

内容的提问来源于stack exchange，提问作者Riyaz Shaikh