c# - Entity Framework Query improved -
i'm trying make android synchronization between client , asp.net mvc server. logic simple, next method receives data dictionary, key = idgroup , value = lastmessageidknown, in end should next messages each group id higher lastmessageidknown (the value of dicctionary).
right iterating map, each key query sql database inefficient, if got n keys can imagine implying.
this 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 improve query need in , optimal way?
thank you.
here's attempt uses predicates make there 1 where against whole collection of messages.
note mocked without database, passing list synchronizedchatmessages function, whereas have context available.
what remains proven way of doing things generates 1 query database (since did in objects only). whole program further, below, first, function showing use of predicates achieve firing where 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 predicates predicate<message> compositepredicate = m => { bool ret = false; foreach (var pred in predlist) { //if of predicates true, composite predicate true (or) if (pred.invoke(m) == true) { ret = true; break; } } return ret; }; //do query var messagesfound = messages.where(m => compositepredicate.invoke(m)).tolist(); //get individual distinct idgroupids var idgroupids = messagesfound.select(x => x.idgroup.id).tolist().distinct().tolist(); //create dictionary return dictionary<int, list<message>> result = new dictionary<int, list<message>>(); foreach (int in idgroupids) { result.add(i, messagesfound.where(m => m.idgroup.id == i).tolist()); } return result; } here 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 predicates predicate<message> compositepredicate = m => { bool ret = false; foreach (var pred in predlist) { //if of predicates true, composite predicate true (or) if (pred.invoke(m) == true) { ret = true; break; } } return ret; }; //do query var messagesfound = messages.where(m => compositepredicate.invoke(m)).tolist(); //get individual distinct idgroupids var idgroupids = messagesfound.select(x => x.idgroup.id).tolist().distinct().tolist(); //create dictionary return dictionary<int, list<message>> result = new dictionary<int, list<message>>(); foreach (int 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(); } } }
Comments
Post a Comment