asked on

Linq - Query from two different tabels

Hi Experts!

Im making a new website in ASP.NET MVC 3 (C#), using MS SQL 2008 as database.

In my database I have two tabels, named Users and Messages.

The Users tabel have these columns:

- Id (PK)
- Username (varchar)
- Age (varchar)
- City (varchar)

The Messages tabel have these columns:

- Id (PK)
- SentTo (int - contains the Id of a user from Users)
- SentBy (int - contains the Id of a user from Users)
- Content (text - contains the content of the message)
- SentDate (datetime - time of sent)

If a user gets 20 messages from another user, I would like the inbox to only show the user one time instead of 20 times. When the user then select the specified sender I redirect the user to another page that shows all messages from the specified user.

In the overview I want to display the Username of the sender, the Content of the last message and the SentDate. (Like facebook).

I have tried different things, but im new to this, so I only got to this code as shown below, where I still needs to make a join in the Users tabel to get the Username, and I still needs to get the Messages.SentDate and Messages.Content.

var Messages = (from r in db.Messages
where r.SentTo == Basic.SessionUserID()
select r.SentBy).Distinct().ToList();

Is it possible to do what I want in just one query? So it will return Users.Username, Messages.Content and Messages.SentDate.

I hope there is some Experts out there who can help me :-)

Kind regards

Jens

ASKER CERTIFIED SOLUTION

brutaldev

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

brutaldev

To elaborate a bit more, you can use the same list from your previous LINQ query to get all the messages for a specific user:

// Will get a list of messages (with the four properties you want) that "Bob" has sent.
var messagesFromBob = info.Where(i => i.Username == "Bob");

Open in new window

CSharpDK

ASKER

Hi Brutaldev,

Thank you very much for your respons, but I still need a little help.

Now I use this:

// The query that will give you an anonymous type with all your required fields for all messages (use in your second screen as well)
int UserId = Basic.SessionUserID();
var info = (from m in db.Messages
	join u in db.Users on m.SentBy equals u.Id
	where m.SentTo == UserId
	select new { m.SentBy, u.Username, m.Content, m.SentDate }).ToList();
            
// Get only distinct usernames
var list = info.Select(i => i.Username).Distinct().ToList();

return View(list);

Open in new window

Bit now I only get the username to the view page. But its correct that the users are only shown once.

On the view page I use this foreach:

@foreach (var item in Model) 
{
    <li>
		<img src="/Content/Images/img4.jpg" alt="" />
		<div class="right_info_besked">
			<h3 class="fameli_besked"><span>@item</span> 6 min<p class="slet_besked">Slet samtalen</p></h3>
			<p>balalsd</p>
		</div>
	</li>
}

Open in new window

It would be very great if I could use @item.Username, @item.Content item.SentDate etc. on the view page. Is that possible?

Kind regards

Jens

brutaldev

Unfortunately, if you include the date then the call to Distinct will introduce more than one record per user since the combination of the two won't be distinct. If that's what you want you may as well pass in all the info in info variable. Make sense?

CSharpDK

ASKER

I got it fixed with help from your post. Thanks a lot!

I did it like this, i dont know if thats the best way, but i works for now :-)

var list = new List<MessagesOveriew>();
            int UserId = Basic.SessionUserID();

            var Messages = (from m in db.Messages
                        join u in db.Users on m.SentBy equals u.Id
                        where m.SentTo == UserId && m.DeletedSentTo == false
                        orderby m.Id descending
                        select new { u.Username, m.Content, m.SentDate }).ToList();

            foreach (var item in Messages)
            {
                int info = list.Where(u => u.Username == item.Username).Count();
                
                if (info == 0)
                {
                    var obj = new MessagesOveriew();
                    obj.Username = item.Username;
                    obj.SentDate = item.SentDate;
                    obj.Content = item.Content;
                    list.Add(obj);
                }
            }

            return View(list);

Open in new window

Thanks a lot for your help!

Kind regards

Jens

brutaldev

If you have an object that models the structure (MessagesOveriew) you might want you can skip the anonymous type altogether and create your type directly in the LINQ statement:

// Get message overview objects.
var messagesOverviews = 
  from m in db.Messages
  join u in db.Users on m.SentBy equals u.Id
  where m.SentTo == Basic.SessionUserID() && m.DeletedSentTo == false
  orderby m.Id descending
  select new MessagesOveriew 
         { Username = u.Username, 
           Content = m.Content,
           SentDate = m.SentDate
         }).ToList();

var list = new List<MessagesOveriew>();
// Get unique usernames from the overviews.
foreach (var uniqueUsername in messagesOverviews.Distinct(m => m.Username))
{
  // Add the first overview you can find for each unique username.
  list.Add(messagesOverviews.First(m => m.Username == uniqueUsername));  
}

return View(list);

Open in new window

There could be even better ways, but this is clean, reads well and avoids an additional object creation step.