Accessing additional properties of an Identity user when retrieving data from the database using EF and Linq

Question

Lets say I added a couple of additional properties to the default User when using asp.net identity:

public class ApplicationUser : IdentityUser
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

I am aware that in my asp.net MVC controller I can simply do the following to get the current logged in user's name:

User.Identity.Name

So when saving to the database I can simply pass User.Identity.Name to my repository along with the object I am saving so the CreatedBy field can be populated.

Now lets say I am retrieving items from the database which have a field of CreatedBy that contains a string of the username, but I want to display Created by : FirstName + LastName in the View.

How do I obtain this extra information? If I was using pure SQL I would do an INNER JOIN on the AspNetUsers table where CreatedBy=Username and simply retrieve the FirstName and LastName in a custom column called CreatedByFullName.

Since I am using Entity Framework now along with the latest version of ASP.NET Identity I am a bit confused at how we are expected to retrieve user information to display in the View of our pages. Is it a matter of doing a join with linq in my repository or simply adding an object to each of my properties called ApplicationUser or is there better ways?

Answer 1

Assumptions:

• You have a single tabled called ApplicationUser that contains all your users.
• This table has an Id column(int) that you are reusing to store lookups in other tables.

Other classes (what I call uni-directional navigation properties):

public class BookContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public Dbset<ApplicationUser> Users { get; set; }

    public overridee OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Book>()
        .HasRequired(b => b.CreatedByUser)
        .WithMany()
        .HasForeignKey(b => b.CreatedBy);
    }
}

public class Book
{
  public int CreatedBy { get; set; }
  public virtual ApplicationUser CreatedByUser { get; set; }
}

Then you'd simply

using (var bookContext = new BookContext())
{
  var firstBookWithRelatedUser bookContext.Books
    .Include(b => b.CreatedByUser)
    .First();
}

Something like that. I recommend reading the Entity Framework Documentation. Granted the above code I pretty much just wrote off the top of my head so I may not be exactly right.

If you wanted, what I call, Bi-Directional navigation properties:

public class ApplicationUser : IdentityUser
{
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public ICollection<Book> Books { get; set; }
}

then

    public overridee OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Book>()
        .HasRequired(b => b.CreatedByUser)
        .WithMany(u => u.Books)
        .HasForeignKey(b => b.CreatedBy);
    }

Then you'd simply

using (var bookContext = new BookContext())
{
  var firstUserWithAllRelatedBooks = bookContext.Users
    .Include(u => u.Books)
    .First();
}

It really just depends on your needs. But becareful, you can end up with a Giant God DbContext that is aware of all relationships...

Answer 2

Sample EF query will look like below -

var result = (from tab in db.YourTable
            join user in db.AspNetUsers on user.username equals tab.CreatedBy                
            select new {YourTableObj = tab, CreatedByFullName = user.FirstName + " " + user.LastName).ToList();