You're not alone. Data modeling masks so much of the goings on that it can be tricky figuring out what is right and wrong.

There's nothing wrong with using LINQ inner join in the sense that it works and it gets you what you need.

However, adding ICollection to the data model for FileTree describes a permanent relationship that can be observed when reading the code, and used whenever you feel like it (which will probably be often, as they're so closely related). i.e. It not only provides the same functionality as the join, but it explains the relationship to other programmers without needing comments or reading code samples.

To use these kind of relationships, most Object Relational Mappings (Entity Framework and NHibernate being two examples of ORM) require that the models specify a primary key in order for them to hook up the foreign key between the child table in the collection and the parent table that has the collection.

So, long story short, use ICollection, but then also specify a primary key on your model for File.

• I've added a [Key] annotation to File, but it doesn't help. And as I've understood from the tutorials, MVC is supposed to know what my key is, because it is named FileID. I suppose I might need to add these annotations to the Identity classes as well. 

  – Simon Verbeke                                        

   Nov 10, 2014 at 20:31                                    
• Sorry, yes, you will need to do that. If you're wondering why, it's because most ORM implement some level of caching of objects to prevent repeatedly hitting the database. The only way they can reasonably identify whether an object has already been loaded is to check for it by Key. 

  – Steve Lillis                                        

   Nov 10, 2014 at 21:00                                    

why do you want to join AspNetUsers you are not selecting any data from AspNetUsers thats just an extra joining.

you need just this

SELECT f.FileName, f.FileSize
FROM Files f
INNER JOIN FileTree ft ON ft.FolderID = f.FolderID
WHERE ft.UserID = @id AND ft.FolderPath = @path;

Your EF LINQ query is going be be something like'

var results = (from F in Files
               join FT in FileTree on F.FolderID equals FT.FolderID
               where FT.UserID == "Your User ID" && FT.FolderPath == "SomePath"
               Select new { F.FileName, F.FileSize}).ToList();

• The point was not my SQL query - it was just an example of what I'd like to achieve. I just don't know what code I'm supposed to put where to get my page working. Your suggestion might work, but I can't get a scaffolding set up, because of some mistake elsewhere. 

  – Simon Verbeke                                        

   Nov 10, 2014 at 21:20                                    
• Did you modify IdentityUserRole? I should have a key by default form ASPNET idenity and during Scaffolding you mean when you are generating a view for a model? If so which model are you trying to bind to the view? 

  – HaBo                                        

   Nov 10, 2014 at 21:41                                     
• I did not modify anything related to Identity. There is an implementation of the model in my project, but that was generated by the sample project (which generates any time you create a new project). Scaffolding is when you right click Controllers > Add > New Scaffolded Item. This generates a controller and views. The model I'm trying to bind is FileTree, which has links to File and IdentityUser. 

  – Simon Verbeke                                        

   Nov 10, 2014 at 21:53                                    
• Can you try creating the view manually. instead of Scaffolded 

  – HaBo                                        

   Nov 10, 2014 at 21:55                                    

For the Identity Stuff you can do this:

using System;

using Microsoft.AspNet.Identity.EntityFramework;

/// <summary>
/// The guid role.
/// </summary>
public class GuidRole : IdentityRole<Guid, GuidUserRole>
{
    /// <summary>
    /// Initializes a new instance of the <see cref="GuidRole"/> class.
    /// </summary>
    public GuidRole()
    {
        this.Id = Guid.NewGuid();
    }

    /// <summary>
    /// Initializes a new instance of the <see cref="GuidRole"/> class.
    /// </summary>
    /// <param name="name">
    /// The name.
    /// </param>
    public GuidRole(string name)
        : this()
    {
        this.Name = name;
    }
}

public class GuidUserRole : IdentityUserRole<Guid> { }
public class GuidUserClaim : IdentityUserClaim<Guid> { }
public class GuidUserLogin : IdentityUserLogin<Guid> { }

/// <summary>
/// The user.
/// </summary>
public class User : IdentityUser<Guid, GuidUserLogin, GuidUserRole, GuidUserClaim>
{
    public User()
    {
        this.Id = Guid.NewGuid();
    }
    /// <summary>
    /// Gets or sets the first name.
    /// </summary>
    public string FirstName { get; set; }

    /// <summary>
    /// Gets or sets the last name.
    /// </summary>
    public string LastName { get; set; }
}

So here you can see that I have made all my Identity Models have Guid as a primary key type. You can easily change that to int , and I reckon that is the way to go!

If you have to do complicated joins, you should leave EF out of it, because it is simply an ORM. It can't replace SQL. You can however create a lovely SQL join in code and have EF execute that query!

using (var context = new ApplicationDbContext())
{
    var result = context.Database.SqlQuery<SomeDtoToHoldTheData>(
                     "SOME LOVELY SQL" + 
                     "SELECT * FROM DATABASE TABLE" +
                     "EVEN PARAMS GO @HERE",
                     new SqlParameter("Here", "My Param"));
}

So I would suggest looking at this first maybe?

Obviously don't concatenate strings with the +, they are immutable.

So lets do your query:

public class FileResultDto
{
    public string FileName {get;set;}
    public decimal FileSize {get;set;}
}

var query = "SELECT f.FileName, f.FileSize" +
            "FROM Files f" +
            "INNER JOIN FileTree ft ON ft.FolderID = f.FolderID" +
            "INNER JOIN AspNetUsers u ON ft.UserID = u.Id" +
            "WHERE u.id = @id AND ft.FolderPath = @path;";

var userIdParam = new SqlParameter("id", userId);
var pathParam = new SqlParameter("path", path);

var result = context.Database.SqlQuery<FileResultDto>(query, userIdParam, pathParam);

If you don't want to go this way. The LINQ I would guess could go something like this:

var fileTrees = context.FileTrees.Where(f => f.FolderPath == folderPath && f.UserID == userId)
                   .GroupJoin(
                         context.Files
                         ft => ft.FolderID,
                         f => f.FolderID,
                         (fileTree, files) => { fileTree, files });

var users = context.Users.FirstOrDefault(u => u.Id == userId);

So in fileTrees is a list of anonymous objects holding a FileTree entity and a list of File entities. Based on you current model that is.