[RESOLVED] What's the best way to replicate this code (LINQ, Datatables and n-tier)

[Nitesh]

Hi Guys,

I am referencing a project so I can create a messaging system on my website.
http://www.packtpub.com/article/aspd...ssaging-system

Can someone please advise me as to how I would replicate this function in my dal and bll so that I can get the same output. In the example they use a datacontext. I mostly return datatables in frim my DAL.

public List<MessageWithRecipient> GetMessagesByAccountID(Int32 AccountID, Int32 PageNumber, MessageFolders Folder)
        {
            List<MessageWithRecipient> result = new List<MessageWithRecipient>();
            using(FisharooDataContext dc = conn.GetContext())
            {
                IEnumerable<MessageWithRecipient> messages = (from r in dc.MessageRecipients 
                                                              join m in dc.Messages on r.MessageID equals m.MessageID 
                                                              join a in dc.Accounts on m.SentByAccountID equals a.AccountID
                                                 where r.AccountID == AccountID && r.MessageFolderID == (int)Folder
                                                 orderby m.CreateDate descending
                                                 select new MessageWithRecipient()
                                                            {
                                                                Sender = a,
                                                                Message = m,
                                                                MessageRecipient = r
                                                            }).Skip((PageNumber - 1)*10).Take(10);
                result = messages.ToList();
            }
            return result;
        }

here is an example of how I return data most times:

Public Function GetMessageRecipientsByMessageID(ByVal MessageID As Integer) As DataTable
            Using con As New MySqlConnection(strCon)

                Dim cmd As MySqlCommand = New MySqlCommand("SELECT * FROM messagerecipients WHERE (MessageID = ?MessageID);", con)

                cmd.Parameters.AddWithValue("?MessageID", MessageID)

                con.Open()

                Dim reader As MySqlDataReader = cmd.ExecuteReader

                _data.Load(reader)

                con.Close()
                reader.Close()
                cmd.Dispose()

                Return _data
            End Using
        End Function

Please help me as this is a bit out of my league.

[MattP]

Are you wanting to use a DataContext and return a List(Of MessageWithReceipt) or do you want the LINQ statement converted to a SQL query so you can return a DataTable?

[Nitesh]

Hi MattP,

I need some help adapting the code I posted to the way I do things. I don't use a DataContext. I return datatables. I don't know whether I should return a datatable of messages from my messages table, then return a datatable with all messagerecipients from the messagerecipients table, and then join those datatables using LINQ. Please advise

[jmcilhinney]

That LINQ query returns a list of objects that each have Sender, Message and MessageRecipient properties. That means that you need to return a DataTable with Sender, Message and MessageRecipient columns. You can see the joins, the ordering and the projection in the LINQ query. Just use a DataAdapter with a SelectCommand containing SQL that performs the same joins, ordering and projection. You may or may not want to perform the paging but, if you do, you'll want to do that on the database, not locally, so that means in the SQL code too.

[Nitesh]

Thanks JM,

I am mucg clearer on what needs to be done but I still need help.I won't be needing paging for now. I've Created a class that has the properties I need to return:

Imports System.Web.Security
Namespace NS
    Public Class MessageWithRecipient
        Private m_Sender As MembershipUser
        Private m_Message As Message
        Private m_MessageRecipient As MessageRecipient

        Public Property Sender As MembershipUser
            Get
                Return m_Sender
            End Get
            Set(value As MembershipUser)
                m_Sender = value
            End Set
        End Property
        Public Property Message As Message
            Get
                Return m_Message
            End Get
            Set(value As Message)
                m_Message = value
            End Set
        End Property
        Public Property MessageRecipient As MessageRecipient
            Get
                Return m_MessageRecipient
            End Get
            Set(value As MessageRecipient)
                m_MessageRecipient = value
            End Set
        End Property
        Public Sub New()
            m_Sender = Nothing
            m_Message = Nothing
            m_MessageRecipient = Nothing
        End Sub
        Public Sub New(ByVal Sender As MembershipUser, ByVal Message As Message, ByVal MessageRecipient As MessageRecipient)
            m_Sender = Sender
            m_Message = Message
            m_MessageRecipient = MessageRecipient
        End Sub
    End Class
End Namespace

I've written the query:

SELECT r.*, m.*, u.* FROM MessageRecipients r
JOIN messages m ON r.MessageID = m.MessageID
JOIN users u ON m.SentByUserID = u.UserID
WHERE r.UserID = 2
ORDER BY m.CreatedDate DESC

I need help with the red highlighted part:

IEnumerable<MessageWithRecipient> messages = (from r in dc.MessageRecipients 
                                                              join m in dc.Messages on r.MessageID equals m.MessageID 
                                                              join a in dc.Accounts on m.SentByAccountID equals a.AccountID
                                                 where r.AccountID == AccountID && r.MessageFolderID == (int)Folder
                                                 orderby m.CreateDate descending
                                                 select new MessageWithRecipient()
                                                            {
                                                                Sender = a,
                                                                Message = m,
                                                                MessageRecipient = r
                                                            }).Skip((PageNumber - 1)*10).Take(10);
                result = messages.ToList();

If I use a Datareader, how can I then return a list of MessagesWithRecipients.

[jmcilhinney]

First up, look at the bit you've highlighted in red. It's the SELECT clause of the query and it's getting three values only per record. Now look at your SQL query. How many values per record are you getting the the its SELECT clause? If you use a DataReader then you simply read each record, create an instance and populate its properties from the fields of the record.

[Nitesh]

Thanks JM,

Here is what I have come up with. Yet to be tested:

Public Function GetMessagesByUserID(ByVal userid As Integer) As List(Of MessageWithRecipient)
            Dim result As New List(Of MessageWithRecipient)()

            Using con As New MySqlConnection(strCon)

                Dim cmd As MySqlCommand = New MySqlCommand("SELECT r.MessageRecipientID, r.MessageID,r.MessageRecipientTypeID,r.UserID,r.CreatedDate,r.MessageFolderID,r.MessageStatusTypeID, " & _
                                                           "m.SentByUserID,m.Subject,m.Body,m.CreatedDate As `MessageCreatedDate`,m.MessageTypeID, " & _
                                                           "u.UserID FROM MessageRecipients r JOIN messages m ON r.MessageID = m.MessageID JOIN users u ON m.SentByUserID = u.UserID WHERE(r.UserID = ?UserID) ORDER BY m.CreatedDate DESC", con)

                cmd.Parameters.AddWithValue("?UserID", userid)

                con.Open()

                Dim reader As MySqlDataReader = cmd.ExecuteReader

                While reader.Read
                    result.Add(New MessageWithRecipient(Membership.GetUser(reader.GetInt32("UserID")), _
                                                         New Message(reader.GetInt32("MessageID"), reader.GetInt32("SentByUserID"), reader.GetString("Subject"), reader.GetString("Body"), reader.GetDateTime("MessageCreatedDate"), reader.GetInt32("MessageTypeID")), _
                                                         New MessageRecipient(reader.GetInt32("MessageRecipientID"), reader.GetInt32("MessageID"), reader.GetInt32("MessageRecipientTypeID"), reader.GetInt32("UserID"), reader.GetDateTime("CreatedDate"), reader.GetInt32("MessageFolderID"), reader.GetInt32("MessageStatusTypeID"))))

                End While


                con.Close()
                reader.Close()
                cmd.Dispose()


            End Using

            Return result.ToList
        End Function

Please check it for me

I really don't understand the LINQ query in the example. I know their MessagesWithRecipient class returns three objects of type Acount,Message and MessageRecipient respectively.

    public class MessageWithRecipient
    {
        public Account Sender { get; set; }
        public Message Message { get; set; }
        public MessageRecipient MessageRecipient{ get; set; }
    }

Then their LINQ returns a list of those objects. What I don't understand is how a gets cast to type Account, m to type Message, r to type MessageRecipient in the following code:

IEnumerable<MessageWithRecipient> messages = (from r in dc.MessageRecipients 
                                                              join m in dc.Messages on r.MessageID equals m.MessageID 
                                                              join a in dc.Accounts on m.SentByAccountID equals a.AccountID
                                                 where r.AccountID == AccountID && r.MessageFolderID == (int)Folder
                                                 orderby m.CreateDate descending
                                                 select new MessageWithRecipient()
                                                            {
                                                                Sender = a,
                                                                Message = m,
                                                                MessageRecipient = r
                                                            }).Skip((PageNumber - 1)*10).Take(10);

[MattP]

The Entity Framework does that from the DataModel (Fisheroo.edmx in the article) which handles the DTOs and relationships.

I'd recommend looking here Working with Data (Entity Framework Tutorial) to get a better understanding of what is happening.

[jmcilhinney]

Looking more closely, I see that misread the original query slightly. I thought that it was returning only three columns but they are actually three entities. That means that you were correct to project all columns from the joined tables. Sorry about that.

[Nitesh]

thanks guys. No need to apologise JM I should have mentioned it in the first post