CODEWITHSUNDEEP
c#sqllinqentity-framework
VansFannel
VansFannel

Reputation: 45931

Join two tables to get User.Name

I'm developing a WCF RESTful service with C#, .NET Framework 4.0 and Entity Framework Code First.

These are my tables:

enter image description here

I want to select this on database:

SELECT PostLine.UserId, PostLine.Description, PostLine.DateUtc, User.Name 
FROM PostLine, User
WHERE User.UserId == PostLine.UserId

And adding a filter for PostLine.DateUtc.

Now, I do this:

DateTime fourDaysAgo = DateTime.Now.Date.AddDays(-4);

var postLines =
    context.PostLines.Where(p => DateTime.Compare(p.DateUtc, fourDaysAgo) > 0);

But my problem is that I don't know how to join User table to get User.name.

How can I join User table to get User.Name?

Upvotes: 0

Views: 84

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

DateTime fourDaysAgo = DateTime.Now.Date.AddDays(-4);

var postLines =
    context.PostLines
             .Join(context.User,
                   p => p.UserId,
                   u => u.UserId,
                   (p, i) => new {p.UserId, p.Description, p.DateUtc, u.Name})
             .Where(p => DateTime.Compare(p.DateUtc, fourDaysAgo) > 0);

Upvotes: 0

Zbigniew
Zbigniew

Reputation: 27604

Make sure that your PostLine class has a property of User type:

public class PostLine
{
    // your properties

    public User Author { get; set; }
}

Then:

string authorOfFirstPostLine = context.PostLines.First().Author.Name;

Upvotes: 1

Related Questions