SQL data projection with nested query in ef-core

Question

there is a table of messages. scheme:

CREATE TABLE [dbo].[Messages] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [DateCreate]  DATETIME2 (7)  DEFAULT (getdate()) NOT NULL,
    [SenderId]    INT            NOT NULL,
    [RecipientId] INT            NOT NULL,
    [TextMessage] NVARCHAR (MAX) NULL,
    [IsReaded]    BIT            NOT NULL,
    CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED ([Id] ASC)
);

I want to get a table:

sender_id [int] - sender id key
all_count_messages [int] - number of messages from the sender
count_unreaded_messages [int] - number of unread messages from the sender
most_unreaded [DateTime] - the date of the most old unread messages

I also need group by [sender_id] and sort the output. Good. I make a simple request:

SELECT
    message.SenderId AS sender_id,
    COUNT(*) AS all_count_messages,
    SUM(CAST(
        CASE
            WHEN IsReaded = 0
                     THEN 1
                  ELSE 0
             END AS int)) as count_unreaded_messages,
    MIN(message.DateCreate) AS most_unreaded
FROM
    Messages AS message
GROUP BY
    message.SenderId
ORDER BY
    most_unreaded

demo result:

sender_id   all_count_messages  unreaded_messages   most_unreaded
2   3   2   2019-08-15 20:03:59.0000000
1   9   8   2019-08-15 20:04:59.0000000

the answer suits me. how to describe it on EFCore?

try it

var chats = from my_messages in db.Messages
                             group my_messages by my_messages.SenderId into g
                             select
                             new
                             {
                                 sender_id = g.Key,
                                 all_count_messages = g.Count(),
                                 unreaded_messages = from sub_messages in db.Messages where sub_messages.SenderId == g.Key && !sub_messages.IsReaded group sub_messages by sub_messages.SenderId into sub_g select sub_g.Count(),
                                 most_unreaded = from sub_messages in db.Messages where sub_messages.SenderId == g.Key && !sub_messages.IsReaded group sub_messages by sub_messages.SenderId into sub_g select sub_g.Min(x => x.DateCreate)
                             };


                foreach (var chat in chats) // so, too, has tried: chats.Include(x=>x.unreaded_messages).Include(x => x.most_unreaded)
                {

                }

get error in foreach (var chat in chats)

An unhandled exception occurred while processing the request.
ArgumentException: must be reducible node
System.Linq.Expressions.Expression.ReduceAndCheck()

I tried otherwise:

 var chats = db.Messages.AsNoTracking().FromSql(
                "SELECT" +
                "    message.SenderId AS sender_id," +
                "    COUNT(*) AS all_count_messages," +
                "    SUM(CAST(" +
                "      CASE" +
                "        WHEN IsReaded = 0" +
                "        THEN 1" +
                "        ELSE 0" +
                "      END AS int)) as count_unreaded_messages," +
                "    MIN(message.DateCreate) AS most_unreaded " +
                "FROM " +
                "    Messages AS message " +
                "GROUP BY " +
                "    message.SenderId " +
                "ORDER BY" +
                "    most_unreaded ");
foreach (var chat in chats)
                {

                }

get error in foreach (var chat in chats)

InvalidOperationException: The required column 'Id' was not present in the results of a 'FromSql' operation.

Microsoft.EntityFrameworkCore.Query.Sql.Internal.FromSqlNonComposedQuerySqlGenerator.CreateValueBufferFactory(IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader)

thank @ilkerkaran for the link https://stackoverflow.com/a/28627991/2630427 there I found and slightly altered method of dynamic request — Станислав Успенский, Aug 16 '19 at 08:41

score 0 · Answer 1 · answered Aug 16 '19 at 06:28

0

You need to add Id column in the select statement to your query. It should be like

 "SELECT" +
                "    message.Id AS Id," +
                "    message.SenderId AS sender_id," +
...

answered Aug 16 '19 at 06:28

ilkerkaran

4,214
3
27
42

SqlException: Column 'Messages.Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. System.Data.SqlClient.SqlConnection.OnError(SqlException exception, bool breakConnection, Action wrapCloseInAction) – Станислав Успенский Aug 16 '19 at 07:38
I am primarily interested in how to describe it in EFCore. any idea? – Станислав Успенский Aug 16 '19 at 07:41
So you want to convert this query to lambda expression? Your current problem is with the query. You need to add `message.Id` to `group by` clause just like `message.SenderId` – ilkerkaran Aug 16 '19 at 07:46
if I group by id, no grouping will occur. Why do I need ID? – Станислав Успенский Aug 16 '19 at 07:51
You should specify the Primary Key to Entity object when you use the ef context. It is a rule you can't workaround. – ilkerkaran Aug 16 '19 at 07:54
there is a simple query in live SQL that projects the desired data. I can't get such a projection in EFCore? – Станислав Успенский Aug 16 '19 at 07:56
but you can use `Context.Database` and manually open connection and execute your query then put it in a custom type. this links shows how https://stackoverflow.com/a/28627991/2630427 – ilkerkaran Aug 16 '19 at 07:56
I have .net core. The solution is not working `return database.SqlQuery(result Type, sql, parameters);` can't in the context to find the `SqlQuery()` – Станислав Успенский Aug 16 '19 at 08:18
Oh yeah mb. There is a workaround for EfCore like this https://github.com/aspnet/EntityFrameworkCore/issues/2344#issuecomment-172641417 – ilkerkaran Aug 16 '19 at 08:20

Станислав Успенский · Answer 2 · 2019-08-16T08:58:41.930

thank @ilkerkaran for the link https://stackoverflow.com/a/28627991/2630427 there I found and slightly altered method of dynamic request

a little finished realisation .net asp core 2.2:

public class AppDbContext : DbContext
    {

        public DbSet<Message> Messages { get; set; }

        IOptions<AppConfig> app_config;

        public AppDbContext(DbContextOptions<AppDbContext> options, IOptions<AppConfig> _app_config)
            : base(options)
        {
            app_config = _app_config;
            Database.EnsureCreated();
        }


        public IEnumerable<dynamic> DynamicListFromSql(string Sql, Dictionary<string, object> Params = null)
        {
            using (var cmd = Database.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = Sql;
                if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }

                if (Params != null)
                    foreach (KeyValuePair<string, object> p in Params)
                    {
                        DbParameter dbParameter = cmd.CreateParameter();
                        dbParameter.ParameterName = p.Key;
                        dbParameter.Value = p.Value;
                        cmd.Parameters.Add(dbParameter);
                    }

                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        var row = new ExpandoObject() as IDictionary<string, object>;
                        for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
                        {
                            row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
                        }
                        yield return row;
                    }
                }
            }
        }

    }

string query =
                "SELECT" +
                "    message.SenderId AS sender_id," +
                "    COUNT(*) AS all_count_messages," +
                "    (SELECT COUNT(*) FROM Messages AS sub_message WHERE sub_message.SenderId = message.SenderId AND sub_message.IsReaded = 0) AS unreaded_messages," +
                "    (SELECT MIN(sub_message.DateCreate) FROM Messages AS sub_message WHERE sub_message.SenderId = message.SenderId) AS most_unreaded " +
                "FROM " +
                "    Messages AS message " +
                "GROUP BY " +
                "    message.SenderId " +
                "ORDER BY" +
                "    most_unreaded ";
                //"OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY", offset, fetch);

                foreach (var chat in db.DynamicListFromSql(query))
                {

                }

SQL data projection with nested query in ef-core

2 Answers2