linq-to-sql Concat() throwing a System.IndexOutofRangeException

I'm struggling with an exception using linq-to-sql Concat()

I've got 2 tables. The first table, ParsedMessages, has the following fields

* ParsedMessageID (int)
* MessageTypeID (int)
* TextMessage (varchar(max)) 

The second table, ParsedMessageLinks, has the following fields

* ParsedMessageID (int)
* AnotherID (int)
* NumberOfOccurences (int)

This is what I need to achieve using a single linq query but I'm not sure if it's possible or not.

• Through a join, retrieves ParsedMessage records that links to a certain AnotherID. In example SQL and linq code, the AnotherID will have the value 0 just for the purpose of having an example.

• For each ParsedMessage record, I also need the NumberOfOccurences (field of table #2)

• Retrieve only the top(100) ParsedMessage records for each MessageTypeID. So for example, if there is 275 records in ParsedMessages that links to AnotherID==0 where the first 150 records have MessageTypeID == 0 and the remaining 125 records having MessageTypeID == 1, I want my query to end up returning 200 records, the top(100) with MessageTypeID == 0 and the top(100) with MessageTypeID == 1

After a lot of search, I've found that the plain SQL equivalent of I what I want to do is this. I knew that this exists first end, but I tried to find something else without Union all at first and fail to do so (my SQL knowledge is not that good) :

SELECT TOP(100) PM.*, PML.NumberOfOccurences FROM ParsedMessages PM INNER JOIN ParsedMessageLinks PML ON PM.ParsedMessageID = PML.ParsedMessageID WHERE PML.AnotherID = 0 AND PM.MessageTypeID = 0 ORDER BY PM.ParsedMessageID DESC UNION ALL

SELECT TOP(100) PM.*, PML.NumberOfOccurences FROM ParsedMessages PM INNER JOIN ParsedMessageLinks PML ON PM.ParsedMessageID = PML.ParsedMessageID WHERE PML.AnotherID = 0 AND PM.MessageTypeID = 1 ORDER BY PM.ParsedMessageID DESC UNION ALL

SELECT TOP(100) PM.*, PML.NumberOfOccurences FROM ParsedMessages PM INNER JOIN ParsedMessageLinks PML ON PM.ParsedMessageID = PML.ParsedMessageID WHERE PML.AnotherID = 0 AND PM.MessageTypeID = 2 ORDER BY PM.ParsedMessageID DESC

So basically, the only way to retrieve the data I need is to do 3 sql queries in a single pass where only the PM.MessageTypeID is different for each query.

Now I wanted to achieve this using linq-to-sql. After googling, I've found that I could use the Linq Concat() method to reproduce a SQL Union All. Here are some links pointing to what I thought would work : http://blog.benhall.me.uk/2007/08/linq-to-sql-difference-between-concat.html

EF. How to union tables, sort rows, and get top entities?

I end up having this exception : System.IndexOutOfRangeException : "Index was outside the bounds of the array."

Here's the faulty code :

IQueryable<MyObject> concatquery;
int[] allMessageTypeIDs = new int[] { 0, 1, 2 };
for (int mt = 0; mt < allMessageTypeIDs.Length; mt++)
{
    if (mt == 0)
    {
        concatquery = (from pm in db.ParsedMessages
                       join pml in db.ParsedMessageLinks on pm.ParsedMessageID equals pml.ParsedMessageID
                       where pml.AnotherID == 0 && pm.MessageTypeID == allMessageTypeIDs[mt]
                       orderby pm.ParsedMessageID descending
                       select new MyObject
                       {
                           NumberOfOccurences = pml.Occurrences,
                           ParsedMessage = pm
                       }).Take(100);
    }
    else
    {
        concatquery = concatquery.Concat(from pm in db.ParsedMessages
                                        join pml in db.ParsedMessageLinks on pm.ParsedMessageID equals pml.ParsedMessageID
                                        where pml.AnotherID == 0 && pm.MessageTypeID == allMessageTypeIDs[mt]
   开发者_运维知识库                                     orderby pm.ParsedMessageID descending
                                        select new MyObject
                                        {
                                            NumberOfOccurences = pml.Occurrences,
                                            ParsedMessage = pm
                                        }).Take(100);
    }
}
var results = concatquery.ToArray();

I've declared the int array allMessageTypeIDs, for simplicity. But remember that the values it holds may differ, so that's why I've added the for loop. Maybe it's "illegal" to use a Concat() in a loop that way, but I could not find any relevant information on this exception.

The class MyObject basically hold a int (NumberOfOccurences) and a ParsedMessage database object, nothing else.

Any suggestions on what could be wrong with my code that causes the exception?

Thanks Francis

Never use the variable you're looping with in your Linq queries. It just doesn't work. You want to assign a new temporary variable to use instead.

IQueryable<MyObject> concatquery;
int[] allMessageTypeIDs = new int[] { 0, 1, 2 };
for (int mt = 0; mt < allMessageTypeIDs.Length; mt++)
{
    var myItem = allMessageTypeIDs[mt]; // <-- HERE!
    if (mt == 0)
    {
        concatquery = (from pm in db.ParsedMessages
                       join pml in db.ParsedMessageLinks on pm.ParsedMessageID equals pml.ParsedMessageID
                       where pml.AnotherID == 0 && pm.MessageTypeID == myItem
                       orderby pm.ParsedMessageID descending
                       select new MyObject
                       {
                           NumberOfOccurences = pml.Occurrences,
                           ParsedMessage = pm
                       }).Take(100);
    }
    else
    {
        concatquery = concatquery.Concat(from pm in db.ParsedMessages
                                        join pml in db.ParsedMessageLinks on pm.ParsedMessageID equals pml.ParsedMessageID
                                        where pml.AnotherID == 0 && pm.MessageTypeID == myItem
                                        orderby pm.ParsedMessageID descending
                                        select new MyObject
                                        {
                                            NumberOfOccurences = pml.Occurrences,
                                            ParsedMessage = pm
                                        }).Take(100);
    }
}
var results = concatquery.ToArray();