Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Linq - Query from two different tabels

Posted on 2011-10-06
6
Medium Priority
?
742 Views
Last Modified: 2013-11-11
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
0
Comment
Question by:CSharpDK
  • 4
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
brutaldev earned 1000 total points
ID: 36927372
I'll take a stab at it, you need to get all the info first before filtering the data further for distinct users. you can easily reference the messages by querying the info variable.

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

Open in new window

0
 
LVL 11

Expert Comment

by:brutaldev
ID: 36927395
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

0
 

Author Comment

by:CSharpDK
ID: 36930212
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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 11

Expert Comment

by:brutaldev
ID: 36933008
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?
0
 

Author Comment

by:CSharpDK
ID: 36933825
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
0
 
LVL 11

Expert Comment

by:brutaldev
ID: 36935610
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month21 days, 6 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question