Join on composite key with Linq and Entity Framework
When joining on a copmosite key, the names of the properties of the annonymus type that you are using as selectors must match. The property names on line 18 and 19 has to be in the same order and have the same names as the ones on lines 23 and 24 in the code below.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| public IArticleDtoResult GetArticle(string articleNumber) | |
| { | |
| var result = _db.article | |
| .Where(a => a.str_no == articleNumber) | |
| .Join( | |
| _db.article_imported_link.DefaultIfEmpty(), | |
| a => a.id, | |
| ail => ail.int_article, | |
| (article, articleImportedLink) => new | |
| { | |
| article, | |
| articleImportedLink | |
| }) | |
| .Join( | |
| _db.imported_article, | |
| articleLink => new | |
| { | |
| Company = articleLink.articleImportedLink.int_company, | |
| Number = articleLink.articleImportedLink.str_imported_articleno | |
| }, | |
| importedArticle => new | |
| { | |
| Company = importedArticle.int_company, | |
| Number = importedArticle.str_no | |
| }, | |
| (articleLink, importedArticle) => new ArticleDtoResult | |
| { | |
| Name = articleLink.article.str_name, | |
| ArticleNumber = articleLink.article.str_no, | |
| ImportedArticleNumber = importedArticle.str_no, | |
| ImportedName = importedArticle.str_name | |
| }) | |
| .FirstOrDefault(); | |
| return result ?? | |
| throw new Exception($"Article with number '{articleNumber}' not found."); | |
| } |