Retain default order for Linq Contains

Question

I want to retain the default order that comes from sql, after processing by Linq also.I know this question has been asked before. Here is a link Linq Where Contains ... Keep default order.

But still i couldn't apply it to my linq query correctly. could anyone pls help me with this? Thanks!

Here is the query

var x = db.ItemTemplates.Where(a => a.MainGroupId == mnId)
                        .Where(a => a.SubGruopId == sbId)
                        .FirstOrDefault();
var ids = new List<int> { x.Atribute1, x.Atribute2, x.Atribute3, x.Atribute4 };
var y = db.Atributes.Where(a => ids.Contains(a.AtributeId))
                    .Select(g => new
                            {
                               Name = g.AtributeName,
                               AtType = g.AtributeType,
                               Options = g.atributeDetails
                                   .Where(w=>w.AtributeDetailId!=null)
                                   .Select(z => new 
                                         {
                                            Value=z.AtributeDetailId,
                                            Text=z.AtDetailVal
                                         })
                            });

@ Enigmativity, Let's assume Atribute1 denotes "ben", Atribute2 "attr5" and Atribute3 "ian". But after processing y gives this order "ian","ben","attr5". I've no idea based on what this gets sorted. But i want the sequence in intial way. That's "ben","attr5","ian". Pls help.. — Isuru, Oct 22 '15 at 06:01
You do realize, that your database most likely does not guarantee any order of records either if you don't use ORDER BY? — nvoigt, Oct 22 '15 at 06:27
@ nvoigt, No im not storing records in DB based on an order and also according to the above query it fetches records from DB as it is in DB. I checked it. But after processing via linq it changes the sequence.. — Isuru, Oct 22 '15 at 06:38
@Isuru - I wanted to know where the ordering goes wrong. Is it wrong after `db.Atributes`? After `.Where(a => ids.Contains(a.AtributeId))`? After `.Select(...)`? — Enigmativity, Oct 22 '15 at 07:50
@ Enigmativity, Ordering goes wrong after .where(a => ids.Contains(a.AtributeId)).. I think the issue comes with 'contains keyword'.. How can i solve this? — Isuru, Oct 22 '15 at 08:23
@Isuru - Don't do a space after the `@` for alerts. It doesn't work then. — Enigmativity, Oct 22 '15 at 08:32
@Isuru - The `.Contains` causes something like `WHERE t0.parent_id IN (88, 74)` clause to be added to your SQL. It's **not** LINQ doing anything to reorder the query - it's your SQL server. — Enigmativity, Oct 22 '15 at 08:43
@nvoigt, Sorry i made an mistake when i did the comment for you. I put a space after @ and because of that you might have not been notified.. I invite you again to help me with this.. — Isuru, Oct 22 '15 at 08:45
@Enigmativity, I checked the 'ids' variable here and it is same as the record sequence in database. So from the sql it comes without any sorting. I have posted a link with my question. That user also have faced my problem. But the problem is i could't apply that solution to mine correctly. Do you think that solution is applicable to this question? If so could you pls guide me? — Isuru, Oct 22 '15 at 08:52
You will need to `OrderBy` your result to whatever you need. Your database most likely *does not guarantee* an order. Your database has no such thing as a "record sequence". If you copy the records into another database or patch your database or restore it from a backup, your sequence may change. You need it ordered, you call `OrderBy`. — nvoigt, Oct 22 '15 at 09:02
@Isuru - I'm trying to echo nvoigt's sentiments. Because it is SQL doing the ordering you may not have any control over it from the LINQ. Unless you bring the records in to memory before the `.Where(...)`, but that might be a **lot** of records. — Enigmativity, Oct 22 '15 at 09:04
@ nvoigt, But in that question(pls refer the link i provided) he has sloved it using 'join' instead of 'contains'. can this be solved using 'join'. Or in my case is it a different scenario? Actually this is about one record. It has column names 'Atribute1', 'Atribute2', 'Atribute3', 'Atribute4'. I wanted to retain that sequence. If the solution is 'orderby' which should i use as base to order, to retain that sequence? — Isuru, Oct 22 '15 at 09:20
@Enigmativity, Actually this is about one record. It has column names 'Atribute1', 'Atribute2', 'Atribute3', 'Atribute4'. I wanted to retain that sequence. By 'ids' variable haven't i got the values stored in sequence? — Isuru, Oct 22 '15 at 09:23
@Isuru - You're putting a space between the `@` and nvoigt's id. He won't get the alert. — Enigmativity, Oct 22 '15 at 09:24
@nvoigt, oops again i made that mistake. sorry. Pls refer my above comment for you :) — Isuru, Oct 22 '15 at 09:31

Robert McKee · Accepted Answer · 2015-10-22T20:32:23.623

Your assumption is wrong. SQL server is the one that is sending the results back in the order you are getting them. However, you can fix that:

var x = db.ItemTemplates.Where(a => a.MainGroupId == mnId)
                        .Where(a => a.SubGruopId == sbId)
                        .FirstOrDefault();
var ids = new List<int> { x.Atribute1, x.Atribute2, x.Atribute3, x.Atribute4 };
var y = db.Atributes.Where(a => ids.Contains(a.AtributeId))
                    .Select(g => new
                            {  
                               Id = g.AtributeId,
                               Name = g.AtributeName,
                               AtType = g.AtributeType,
                               Options = g.atributeDetails
                                   .Where(w=>w.AtributeDetailId!=null)
                                   .Select(z => new 
                                         {
                                            Value=z.AtributeDetailId,
                                            Text=z.AtDetailVal
                                         })
                            })
     .ToList()
     .OrderBy(z=>ids.IndexOf(z.Id));

Feel free to do another select after the orderby to create a new anonymous object without the Id if you absolutely need it to not contain the id.

PS. You might want to correct the spelling of Attribute, and you should be consistent in if you are going to prefix your property names, and how you do so. Your table prefixes everything with Atribute(sp?), and then when you go and cast into your anonymous object, you remove the prefix on all the properties except AtributeType, which you prefix with At. Pick one and stick with it, choose AtName, AtType, AtOptions or Name, Type, Options.

Thank you very much for the well explained answer. yes that worked! — Isuru, Oct 23 '15 at 19:28

Retain default order for Linq Contains

1 Answers1