Entity Framework query improved

Question

I'm trying to make an android synchronization between client and ASP.NET MVC server. The logic is simple, my next method receives a data dictionary, where key = idGroup and value = LastMessageIdKnown, in the end I should get the next messages for each group what Id is higher than the LastMessageIdKnown (the value of my dictionary).

Right now I am iterating the map, for each key I do a query to my SQL database but this is inefficient, if I got N keys you can imagine what implying.

This is my current method

public Dictionary<int, List<Messages>> SynchronizedChatMessages(Dictionary<int, int> data)
{
    Dictionary<int, List<Messages>> result = new Dictionary<int, List<Messages>>();


    foreach(int item in data.Keys){
        var idMessage= data[item];
        var listMessages= _context.Messages.Where(x => x.Grupo_ID == item && x.ID > idMessage).ToList();
        result.Add(item,listMessages);
    }
    return result;
}

How can I improve this query to get all what I need in an only and optimal way?

Thank you.

Answer 1

Well it would be nice if this would work, but I doubt it that can be translated to a SQL statement in one go:

var toInsert = 
     from msg in _context.Messages
     group msg by msg.Grupo_ID into g
     where data.Keys.Contains(g.Key)
     select new {
         Item = g.Key,
         Messages = g.Where(x => x.ID > data[g.Key])
     };

I don't think the second Where clause x => x.ID > data[g.Key] can be translated.

So you may need to do this in two passes, like this:

// This is a single SQL query.
var groups = 
     from msg in _context.Messages
     group msg by msg.Grupo_ID into g
     where data.Keys.Contains(g.Key)
     select new {
         Item = g.Key,
         // ordering helps us when we do the in-memory part.
         Messages = g.OrderByDescending(x => x.ID).ToList()
     };

// This iterates the result set in memory
foreach (var g in groups)
    result.Add(
        g.Item, 
        // input is ordered, we stop when an item is <= data[g.Item].
        g.Messages.TakeWhile(m => m.ID > data[g.Item]).ToList())

Answer 2

Here's an attempt that uses Predicates to make it so that there is only one Where against the whole collection of messages.

Note that I mocked this up without a database, so I am passing a List into the SynchronizedChatMessages function, whereas you have the context available.

What remains to be proven is that this way of doing things only generates one query to the database (since I did it in objects only). The whole program is further, below, but first, just the function showing use of predicates to achieve firing the Where only once.

public static Dictionary<int, List<Message>> SynchronizedChatMessages(List<Message> messages, Dictionary<int, int> data)
{
    List<Predicate<Message>> predList = new List<Predicate<Message>>();
    //Built of list of indivIdual predicates
    foreach (var x in data)
    {
        var IdMessage = x.Key;
        var lastMessageId = x.Value;

        Predicate<Message> pred = m => m.IdGroup.Id == IdMessage && m.Id > lastMessageId;
        predList.Add(pred);
    }

    //compose the predicates
    Predicate<Message> compositePredicate = m =>
    {
        bool ret = false;
        foreach (var pred in predList)
        {
            //If any of the predicates is true, the composite predicate is true (OR)
            if (pred.Invoke(m) == true) { ret = true; break; }
        }
        return ret;
    };

    //do the query
    var messagesFound = messages.Where(m => compositePredicate.Invoke(m)).ToList();

    //get the individual distinct IdGroupIds
    var IdGroupIds = messagesFound.Select(x => x.IdGroup.Id).ToList().Distinct().ToList();

    //Create dictionary to return
    Dictionary<int, List<Message>> result = new Dictionary<int, List<Message>>();
    foreach (int i in IdGroupIds)
    {
        result.Add(i, messagesFound.Where(m => m.IdGroup.Id == i).ToList());
    }

    return result;
}

Here is the whole thing:

using System;
using System.Collections.Generic;
using System.Linq;

namespace ConsoleApplication20
{
public class Program
{
    public class Message
    {
        public int Id { get; set; }
        public IdGroup IdGroup { get; set; }
    }

    public class IdGroup
    {
        public int Id { get; set; }
        public List<Message> Messages { get; set; }
    }

    public static Dictionary<int, List<Message>> SynchronizedChatMessages(List<Message> messages, Dictionary<int, int> data)
    {
        List<Predicate<Message>> predList = new List<Predicate<Message>>();
        //Built of list of indivIdual predicates
        foreach (var x in data)
        {
            var IdMessage = x.Key;
            var lastMessageId = x.Value;

            Predicate<Message> pred = m => m.IdGroup.Id == IdMessage && m.Id > lastMessageId;
            predList.Add(pred);
        }

        //compose the predicates
        Predicate<Message> compositePredicate = m =>
        {
            bool ret = false;
            foreach (var pred in predList)
            {
                //If any of the predicates is true, the composite predicate is true (OR)
                if (pred.Invoke(m) == true) { ret = true; break; }
            }
            return ret;
        };

        //do the query
        var messagesFound = messages.Where(m => compositePredicate.Invoke(m)).ToList();

        //get the individual distinct IdGroupIds
        var IdGroupIds = messagesFound.Select(x => x.IdGroup.Id).ToList().Distinct().ToList();

        //Create dictionary to return
        Dictionary<int, List<Message>> result = new Dictionary<int, List<Message>>();
        foreach (int i in IdGroupIds)
        {
            result.Add(i, messagesFound.Where(m => m.IdGroup.Id == i).ToList());
        }

        return result;
    }

    public static void Main(string[] args)
    {
        var item1 = new IdGroup { Id = 2, Messages = new List<Message>() };
        var item2 = new IdGroup { Id = 45, Messages = new List<Message>() };
        var item3 = new IdGroup { Id = 36, Messages = new List<Message>() };
        var item4 = new IdGroup { Id = 8, Messages = new List<Message>() };

        var message1 = new Message { Id = 3, IdGroup = item1 };
        var message2 = new Message { Id = 7, IdGroup = item1 };
        var message3 = new Message { Id = 9, IdGroup = item1 };
        item1.Messages.Add(message1);
        item1.Messages.Add(message2);
        item1.Messages.Add(message3);

        var message4 = new Message { Id = 4, IdGroup = item2 };
        var message5 = new Message { Id = 10, IdGroup = item2 };
        var message6 = new Message { Id = 76, IdGroup = item2 }; 
        item2.Messages.Add(message4);
        item2.Messages.Add(message5);
        item2.Messages.Add(message6);

        var message7 = new Message { Id = 6, IdGroup = item3 };
        var message8 = new Message { Id = 32, IdGroup = item3 };
        item3.Messages.Add(message7);
        item3.Messages.Add(message8);

        var message9 = new Message { Id = 11, IdGroup = item4 };
        var message10 = new Message { Id = 16, IdGroup = item4 };
        var message11 = new Message { Id = 19, IdGroup = item4 };
        var message12 = new Message { Id = 77, IdGroup = item4 }; 
        item4.Messages.Add(message9);
        item4.Messages.Add(message10);
        item4.Messages.Add(message11);
        item4.Messages.Add(message12);

        List<IdGroup> items = new List<IdGroup> { item1, item2, item3, item4 };
        List<Message> messages = new List<Message> { message1, message2, message3, message4, message5, message6,message7, message8, message9, message10, message11, message12};
        Dictionary<int, int> lastMessagesPerItem = new Dictionary<int, int> { { 2, 3 }, { 45, 10 }, { 36, 6 }, { 8, 11 } };

        var result = SynchronizedChatMessages(messages, lastMessagesPerItem);

        var discard = Console.ReadKey();                     

    }
}
}