exception in Linq to sql

Question

my query is :

var ReadAndUnreadMessages =
        (from m in MDB.Messages
         orderby m.Date descending
         where m.ID_Receive == (Guid)USER.ProviderUserKey && m.Delete_Admin == false
         select new AllMessages()
         {
             id = (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).ID_Message,
             parent = (Guid)(LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).ID_Message_Parent,
             sender = (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).ID_Sender,
             receiver = (Guid)USER.ProviderUserKey,
             subject = (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).Subject.Subject1.ToString() == "Other" ?
                           (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).Other_Subject
                           :
                           (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).Subject.Subject1.ToString(),
             body = (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).Body.Length > 26 ?
                     (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).Body.Substring(0, 25) + "..."
                     :
                     (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).Body,
             date = (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).Date.ToShortDateString(),
             read =(LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).IsRead,
             finished = (LoadMessageChildren(m.ID_Message)[LoadMessageChildren(m.ID_Message).Count - 1] as Message).IsFinished,
             count = MessageClass.LoadAll(m.ID_Message).Count
         }).ToList();

and exception is :

The argument 'value' was the wrong type. Expected 'Message'. Actual 'System.Object'.

what does meaning it?

LoadMessageChildren :

public static ArrayList LoadMessageChildren(Guid Parent)
{
    ArrayList arr = new ArrayList();
    Guid id = Parent;
    while (id != Guid.Empty)
    {
        arr.Add(LoadMessage(id));
        try
        {
            id = (Guid)MDB.Messages.Single(a => a.ID_Message_Parent == id).ID_Message;
        }
        catch
        {
            id = Guid.Empty;
        }
    }
    return arr;
}

LoadMessage :

public static Message LoadMessage(Guid id)
{
    var mess = from m in MDB.Messages
               where m.ID_Message == id
               select m;

    return mess.Single();
}

Answer 1

The code is unreadable, and as a bad case of code repetition (and multiple executions of LoadMessageChildren).
For starters, consider the following:

from m in MDB.Messages
     orderby m.Date descending
     where m.ID_Receive == (Guid)USER.ProviderUserKey && m.Delete_Admin == false
     let children = LoadMessageChildren(m.ID_Message)
     let lastChildMessage = children.Last()
     select new AllMessages()
     {
         id = lastChildMessage.ID_Message, 
         ...
     }

This may solve your problem, as it is might be caused by using the [] indexer.
Aside from that, it is not clear the posted code is causing the exception.

Answer 2

That's exactly what you'll get yourself into when you get used to copy and paste. Try this instead:

var ReadAndUnreadMessages =
        (from m in MDB.Messages
         orderby m.Date descending
         where m.ID_Receive == (Guid)USER.ProviderUserKey && m.Delete_Admin == false
         let msglist = LoadMessageChildren(m.ID_Message)
         let msg = (Message)m[m.Count-1]
         select new AllMessages()
         {
             id = msg.ID_Message,
             parent = msg.ID_Message_Parent,
             sender = msg.ID_Sender,
             receiver = (Guid)USER.ProviderUserKey,
             subject = msg.Subject.Subject1.ToString() == "Other" ?
                           msg.Other_Subject
                           :
                           msg.Subject.Subject1.ToString(),
             body = msg.Body.Length > 26 ?
                     msg.Body.Substring(0, 25) + "..."
                     :
                     msg.Body,
             date = msg.Date.ToShortDateString(),
             read =msg.IsRead,
             finished = msg.IsFinished,
             count = MessageClass.LoadAll(m.ID_Message).Count
         }).ToList();

Not the ideal code of course but will get you through the night.

Answer 3

The error is most likely caused by the use of the ArrayList.

The problem is that LINQ was designed to work with generic collections that implement the System.Collections.Generic.IEnumerable<T> interface. The ArrayList is a nongeneric collection that internally stores everything as an Object. So when you retrieve something from the ArrayList you need to cast it to a Message. Looking at your error message it looks like somewhere a Message object is expected, but the instance in your ArrayList (an Object) is not casted to a Message object when that reference occurs. Also, the ArrayList does not implement the IEnumerable<T> interface which might get you into trouble in certain situations also.

How to fix it?

I suggest changing the implementation of your LoadMessageChildren to use a generic list (List<Message>):

public static List<Message> LoadMessageChildren(Guid Parent)
{
    List<Message> arr = new List<Message>();
    Guid id = Parent;
    while (id != Guid.Empty)
    {
        arr.Add(LoadMessage(id));
        try
        {
            id = (Guid)MDB.Messages.Single(a => a.ID_Message_Parent == id).ID_Message;
        }
        catch
        {
            id = Guid.Empty;
        }
    }
    return arr;
}

You will have to make also change the code that interacts with the generic list in terms of retrieving/referencing items. But that is just syntax. Since equivalent methods for dealist with lists and items exist.

There are also advantages in terms of performance and compile-time validation for switching from ArrayList to List<T>. The ArrayList is basically an inheritance from version 1.0 of the .Net Framework when there was no support for generics and it just get kept in the framework probably for compatibility reasons. There are greater benefits for using generics.

UPDATED ANSWER:

The "Method 'System.Collections.Generic.List'1[Message] LoadMessageChildren(System.Guid)' has no supported translation to SQL" exception that you are getting is caused by the fact that your LoadMessageChildren method is not mapping to a stored procedure or a user defined function in your database.

You cannot have any regular C# method call inside your LINQ to SQL queries. The LINQ to SQL object model interprets a method found inside your query as either a stored procedure or a user defined function. So the engine is basically looking for a method called LoadMessageChildren that maps to a stored procedure or a user defined function in your database. Because there are no mappings, it tells you that no supported translation to SQL was found. The LINQ to SQL object model link shows you how to use method attributes to map a method that executes a stored procedure.

You have a few choices now:

1. create stored procedures of your regular C# method calls
2. rewrite your LINQ query to use joins to select child messages

Answer 4

The only thing I see you using LoadChildMessages() for in the end is to get the child message count... Unless I am wrong I would think you could write it as a join. You doing a lot of queries with in queries that don't seem necessary and are probably causing multiple hits to the database. My question to that would be why isn't there a relationship in your dmbl/sql database so that LinqToSql knows to create a property as a List<Message> ChildMessages

But here is my take:

var query = from message in MDB.Messges
            join childmessage in MDB.Messages.Where(child => child.ID_Message_Parent == message.ID_Message) into childMessages
            from childMessage in childMessages.DefaultIfEmpty() // This creates a 
            // left outer join so you get parent messages that don't have any children 
            where message.ID_Receive == (Guid)USER.ProviderUserKey && message.Delete_Admin == false
            select new AllMessages()
            {
                id = message.ID_Message,
                parent = message.ID_Message_Parent,
                sender = message.ID_Sender,
                receiver = (Guid)USER.ProviderUserKey,
                subject = message.Subject.Subject1.ToString() == "Other" ?
                           message.Other_Subject
                           :
                           message.Subject.Subject1.ToString(),
                body = message.Body.Length > 26 ?
                     message.Body.Substring(0, 25) + "..."
                     :
                     message.Body,
                date = message.Date.ToShortDateString(),
                read =message.IsRead,
                finished = message.IsFinished,
                count = childMessage.Count() // This might have to be this
                //count = childMessage == null ? 0 : childMessage.Count()
            };

var ReadAndUnreadMessages = query.ToList();

But it's hard to say because I can't run the code... Please respond and let me know if this works.

Note: May I suggest using a class that links to your DataContext.Log property that writes the generated TSQL code to the debugger window. Here is an article on writing your own. It has really help me know when I am making unnecessary calls to the database.