How to find average when instances are grouped or categorized on multiple columns?

There is a table 'Result' with following data:

There is a table 'Result' with following data:

ID   Title   Category  SubCategory  Value
1    Part-1  CatX      A            100
2    Part-1  CatX      B            0
3    Part-1  CatX      C            50
4    Part-1  CatY      A            100
5    Part-1  CatY      B            0
6    Part-1  CatY      C            100
7    Part-2  CatM      A            30
8    Part-2  CatM      B            10
9    Part-2  CatM      C            100
10   Part-2  CatN      A            50
11   Part-2  CatN      B            10
12   Part-2  CatN      C            80

And this is what I am trying to achieve:

Title   SubCategory   AvgValue
Part-1  A          100
Part-1  B          0
Part-1  C          75
Part-2  A          40
Part-2  B          10
Part-2  C          90

Each Title has 3 SubCategories and I need to display the Average Value of each SubCategory for every Title. Please Help. Answer can be a SQL server Query or Linq as I can get the Result table in a List/datatable.

Entity Framework 6, Command Interception & Stored Procedures

I was asked to develop auditing for a system at my work. The system has already been completed. I think EF 6's Command Interception should work well for my purposes.

I was asked to develop auditing for a system at my work. The system has already been completed. I think EF 6's Command Interception should work well for my purposes.

However, there are situations like this where we would want to know who sent a request for leave, and we would like to be able to intercept this Insert query.

using (DataContext context = new DataContext())
    {
      var result = context.CreateLeavePrerequest(
        leaveRequest.LeaveType,
        leaveRequest.StartDate,
        leaveRequest.EndDate,
        leaveRequest.NumberOfDays,
        leaveRequest.EmployeeComment,
        leaveRequest.HasSupportingDocumentation,
        leaveRequest.ResourceTag,
        leaveRequest.RemainingBalance,
        leaveRequest.ApproverResourceTag,
        leaveRequest.CapturerResourceTag,
        leaveRequest.SupportingDocumentID,
        ref id
        );

then the stored procedure is:

CREATE PROCEDURE [dbo].[CreateLeavePrerequest]
(
  @LeaveType VARCHAR(50) ,
  @StartDate DATETIME ,
  @EndDate DATETIME ,
  @NumberOfDays DECIMAL(18, 5) ,
  @EmployeeComment VARCHAR(512) ,
  @SickNoteIndicator BIT ,
  @ResourceTag INT,
  @RemainingBalance DECIMAL,
  @ApproverResourceTag INT,
  @CapturerResourceTag INT,
  @SupportingDocumentID INT,
  @id INT = 0 OUT
)  
AS 
BEGIN
    INSERT  INTO [ESS PER LVE PreRequest]
            ( [Resource Tag] ,
              [Leave Type] ,
              [Start Date] ,
              [End Date] ,
              [No Of Days] ,
              [Employee Comments] ,
              [Sick Note Indicator],
              [Status],
              [Remaining Balance],
              [Approver Resource Tag],
              [Capturer Resource Tag],
              [SupportingDocumentID]
            )
            SELECT  @ResourceTag ,
                    @LeaveType ,
                    @StartDate ,
                    @EndDate ,
                    @NumberOfDays ,
                    @EmployeeComment ,
                    @SickNoteIndicator,
                    'Captured',
                    @RemainingBalance,
                    @ApproverResourceTag,
                    @CapturerResourceTag,
                    @SupportingDocumentID;
SELECT @id
END 

UPDATE:

CreateLeavePrerequest is implemented as follows:

public ISingleResult<CreateLeavePrerequestResult> CreateLeavePrerequest([global::System.Data.Linq.Mapping.ParameterAttribute(Name="LeaveType", DbType="VarChar(50)")] string leaveType, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="StartDate", DbType="DateTime")] System.Nullable<System.DateTime> startDate, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="EndDate", DbType="DateTime")] System.Nullable<System.DateTime> endDate, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="NumberOfDays", DbType="Decimal(18,5)")] System.Nullable<decimal> numberOfDays, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="EmployeeComment", DbType="VarChar(512)")] string employeeComment, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="SickNoteIndicator", DbType="Bit")] System.Nullable<bool> sickNoteIndicator, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="ResourceTag", DbType="Int")] System.Nullable<int> resourceTag, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="RemainingBalance", DbType="Decimal(18,0)")] System.Nullable<decimal> remainingBalance, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="ApproverResourceTag", DbType="Int")] System.Nullable<int> approverResourceTag, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="CapturerResourceTag", DbType="Int")] System.Nullable<int> capturerResourceTag, [global::System.Data.Linq.Mapping.ParameterAttribute(Name="SupportingDocumentID", DbType="Int")] System.Nullable<int> supportingDocumentID, [global::System.Data.Linq.Mapping.ParameterAttribute(DbType="Int")] ref System.Nullable<int> id)
    {
        IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), leaveType, startDate, endDate, numberOfDays, employeeComment, sickNoteIndicator, resourceTag, remainingBalance, approverResourceTag, capturerResourceTag, supportingDocumentID, id);
        id = ((System.Nullable<int>)(result.GetParameterValue(11)));
        return ((ISingleResult<CreateLeavePrerequestResult>)(result.ReturnValue));
    }

UPDATE 2

DBCommandInterceptor registration in Global.asax:

 protected void Application_Start()
 {
     DbInterception.Add(new Auditor());
 }

DBCommandInterceptor implementation:

I implemented this quickly so that I could just see whether I could intercept anything, so it just writes to the Debug window. I have been able to intercept some Select queries, but that's not what we want to audit.

 public class Auditor : IDbCommandInterceptor
{
    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        CreateAuditMessage(command, interceptionContext);
    }
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext&lt;int&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext&lt;DbDataReader&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext&lt;DbDataReader&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext&lt;object&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext&lt;object&gt; interceptionContext)
{
    CreateAuditMessage(command, interceptionContext);
}

public static void CreateAuditMessage&lt;T&gt;(DbCommand command, DbCommandInterceptionContext&lt;T&gt; interceptionContext)
{
    string message;

    var parameters = new StringBuilder();
    foreach (DbParameter param in command.Parameters)
    {
        parameters.AppendLine(param.ParameterName + " " + param.DbType + " = " + param.Value);
    }

    if (interceptionContext.Exception == null)
    {
        message = (parameters.ToString() + "  " + command.CommandText);
    }
    else
    {
        message =  (parameters.ToString() + command.CommandText + "  " + interceptionContext.Exception);
    }

    Debug.WriteLine(message);
}

}

Recently, I've been reading a lot about Entity Framework, but I am not very knowledgeable. I have implemented the IDbCommandInterface and registered it etc. I am able to see some other queries being intercepted, but since the above situation is such that the stored procedure is being called "outside" I can't get hold of the parameters.

This is a simple example. Not all the stored procedures that are called in a similar way in the system are so simple.

What would be the best way to change the above situation so that we can apply the interception and thus the auditing?

Linq Many to many in Entity Framework core

I have classes for building many to many relationships.

I have classes for building many to many relationships.

public class Chat 
{
    [Key]
    public Guid Id { get; set; }
    public ICollection<ApplicationUserChat> UserChats { get; set; }
}

public class ApplicationUserChat
{
[Required]
public string UserId { get; set; }
public ApplicationUser User { get; set; }

public Guid ChatId { get; set; }
public Chat Chat { get; set; }

}

public class ApplicationUser : IdentityUser
{
public ICollection<ApplicationUserChat> UserChats { get; set; }
}

For example, I have a user1 and user2

  1. I need to Select chats for user1 where chat contains a user2
  2. I need to select chat for user1 where chat contains a user2 and not contains another users (like one chat contains only 2 participants)
  3. How I can do this in EF Core?


SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners - Learn MS SQL Server with Examples

SQL Server Tutorial For Beginners, Microsoft SQL Server Tutorial, SQL Server Training. Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples. What is DBMS? What is SQL? What is SQL Server? SQL Server Installation

This Edureka tutorial video on Microsoft SQL Server explains all the fundamentals of MS SQL Server with examples.
The following are the topics covered in this tutorial:

  • What is DBMS?
  • Types of DBMS
  • What is SQL?
  • What is SQL Server?
  • Features of SQL Server
  • SQL Server Installation
  • SQL Server Command Categories
  • SQL Server Data Types
  • DDL Commands
  • Keys in Database
  • Constraints in Database
  • DML Commands
  • Operators
  • Nested Queries
  • Joins
  • Stored Procedures
  • DCL Commands
  • TCL Commands
  • Exception Handling