Select all columns from main table and only 1 column from JOINed table-CodePudding

I have two tables:

 Table1
 Id         ArticleName      ArticleTypeId
 1          Blah Blah        3
 2          Helo Blah        5

and

Table2
ArticleTypeId       TypeName
3                   Business
5                   Construction

I'm trying to Join TableA and TableB on ArticleTypeId and basically return everything from Table1 and TypeName from Table2

Here's what I'm trying to do, but I'm not sure to to edit the SELECT in the statement to include the TypeName

var articles = (from s in _context.Articles
                        join b in _context.ArticleTypes on s.ArticleTypeId equals b.ArticleTypeId
                        select s).ToList();

Or is there an easier way to do this?

Goal:

 Id      ArticleName      TypeName
 1       Blah Blah        Business
 2       Helo Blah        Construction

CodePudding user response：

So you have two tables, a table with Articles (Table1), and a table with ArticleTypes (Table2). I decided to give your tables names that are meaningful, this makes discussion easier.

There is a one to many relationship between Articles and ArticleTypes: Every Article has exactly one ArticleType, namely the Article type that the foreign key ArticleTypeId refers to. Every ArticleType has zero or more Articles that refer to it.

You are using entity framework. If you've followed Entity Framework Coding Conventions, you'll have classes similar to the following.

class Article
{
    public int Id {get; set;}
    public string Name {get; set;}

    // every Article has one ArticleType, namely the one that the foreign key refers to
    public int ArticleTypeId {get; set;}
    public virtual ArticleType ArticleType {get; set;}
}

class ArticleType
{
    public int Id {get; set;}
    public string TypeName {get; set;}

    // every ArticleType has zero or more Articles referring to it (one-to-many)
    public virtual ICollection<Article> Articles {get; set;}
}

In entity framework the non-virtual properties refer to columns of the tables; the virtual properties refer to the relations between the tables (one-to-many, many-to-many, ...)

The foreign key ArticleTypeId is a real column, so the property is non-virtual. Property ArticleType is virtual, because it represents the one-to-many relation.

For completeness your DbContext:

class MyWarehouse : DbContext
{
    public DbSet<Article> Articles {get; set;}
    public DbSet<ArticleType> ArticleTypes {get; set;}
}

I'm trying to Join TableA and TableB on ArticleTypeId and basically return everything from Table1 and TypeName from Table2

After you've defined your classes, your query is easy. The easiest method is using the virtual properties.

Use the virtual properties

Requirement Give me the Id and Name of all Articles, each Article with its TypeName.

using (var wareHouse = new MyWareHouse(...))
{
    var requestedArticles = wareHouse.Articles.Select(article => new
    {
        // Select only the Article Properties that you plan to use
        Id = article.Id,
        Name = article.Name,
        TypeName = article.ArticleType.TypeName,
    });

    // Process the requested Articles before disposing the wareHouse
}

In words: from every Article in the table of Articles fetch the Id, the Name and the one and only TypeName it has.

Entity Framework knows the relation between Articles and ArticleTypes. Because you use the virtual Property Article.ArticleType it knows which join to perform.

Using the virtual properties you can also get each ArticleType together with all Articles that have this ArticleTypes

var constructionArticles = wareHouse.ArticleTypes
.Where(articleType => articleType.TypeName == "construction")
.Select(articleType => new
{
    Id = articleType.Id,
    TypeName = articleType.TypeName,

    // fetch all articles that have this TypeName
    Articles = articleType.Articles.Select(article => new
    {
        Id = article.Id,
        Name = article.Name,

        // no need to fetch the foreign key, you already got this value
        // ArticleTypeId = article.ArticleTypeId,
    })
    .ToList(),
})
.ToList();

Entity framework knows the relation and will do the proper (Group-)join for you.

Did you notice how natural using the virtual properties feel?

Do the Join yourself

Some people don't want to use the virtual properties, they prefer to do the (Group-)joins themselves.

Use the overload of method Join that has a parameter resultSelector, so you can specify the desired result.

// Join Articles with ArticleTypes
var requestedArticles = wareHouse.Articles.GroupJoin(wareHouse.ArticleTypes,

// from every Article take the foreign key
article => articleTypeId,

// from every ArticleType take the primary key
articleType => articleType.Id,

// parameter resultSelector:
// take each article and its one and only matching ArticleType to make one new
(article, articleType) => new
{
    Id = article.Id,
    Name = article.Name
    TypeName = articleType.TypeName,
});

If you have a one-to-many relation, like Schools with their Students, Customers with their Orders, or ArticleTypes with their Articles, use GroupJoin and start at the 'one' side. If you want Students, each Student with the School he attends, use Join, and start at the 'many' side.

var schoolsWithTheirStudents = dbContext.Schools
.Where(school => school.City == ...)     // if you don't want all Schools
.GroupJoin(dbContext.Students,

// from every School take the primary key
school => school.Id,

// from every Student take the foreign key to the School he attends
student => student.SchoolId,

// resultSelector: take each Schools with its matching Students to make one ned
(school, studentsWhoAttendThisSchool) => new
{
    // Select only the School properties that you plan to use:
    Id = school.Id,
    Name = school.Name,
    Address = school.Address,
    ...

    Students = studentsWhoAttendThisSchool.Select(student => new
    {
        Id = student.Id,
        Name = student.Name,
        ...
    })
    .ToList(),
});

})