I'm implementing asp.net core project. I have 3 tables Apiapp, ApiAppHistory and EntityType. There are three fields with the names SentType, Status and Reason in ApiAppHistory and those fields are of kind Id (int type) in APIApphistory. I joined ApiApp and ApiAppHistory tables in order to get those three fields from ApiAppHistory but because they are of kind int and are unclear when showing the result to the user, I join them with EntityType table which has their related name in the name column. I mean, in the select part of my query, in addition to ApiApp fields I also need to have SentType, Status and Reason name fields and their names are in entityType table. For instance, the fields SentType, Status and Reason in APIApphistory will have values 4,5,1. so for obtaining their related names I have to join APIAppHistoy with EntityType table to get their related name that are:d,e,a. But I don't know how to do it. Here below is my incomplete query:
var qq = _context.Apiapp
.Include(a => a.Api)
.Include(a => a.Application)
.Include(a => a.Data);
var t12 = (from r in qq
from b in _context.ApiAppHistory
from s in _context.EntityType
where r.LastRequest== b.Id && b.SentType == s.Id
&& b.Reason == s.Id
&& b.Status == s.Id
select new { r, b.Reason (instead I want its related s.name), b.SentType (instead I want its related s.name), b.Status (instead I want its related s.name)});
I want in select part of my query, obtain name of the those 3 fields that I specified in the EntityType table. However, I don't know how to join ApiAppHistory and EntityType such that getting the related names for SentType, Reason ans Status from EntityType. Because by joining APIApp and ApiAppHistory I can just get those field's Id. I appreciate if someone helps me.
Here is my EntityType table:
Id Name EntityKey
1 a Reason
2 b Reason
3 c SentType
4 d SentType
5 e Status
6 f Status
we don't use key from EntityType in the query. The key values in EntityType are just for clarifying the meaning of the other columns to the developer
What I have tried:
var qq = _context.Apiapp
.Include(a => a.Api)
.Include(a => a.Application)
.Include(a => a.Data);
var t12 = (from r in qq
from b in _context.ApiAppHistory
from s in _context.EntityType
where r.LastRequest== b.Id && b.SentType == s.Id
&& b.Reason == s.Id
&& b.Status == s.Id
select new { r, b.Reason (instead I want its related s.name), b.SentType (instead I want its related s.name), b.Status (instead I want its related s.name)});
public partial class ApiAppHistory
{
public int Id { get; set; }
public int? SentType { get; set; }
public int? Reason { get; set; }
public int? Status { get; set; }
public virtual Apiapp ApiApp { get; set; }
public virtual EntityType StatusNavigation { get; set; }
public virtual EntityType SentTypeNavigation { get; set; }
public virtual EntityType ReasonNavigation { get; set; }
}
public partial class EntityType
{
public EntityType()
{
ApiAppHistoryStatusNavigation = new HashSet<ApiAppHistory>();
ApiAppHistorySentTypeNavigation = new HashSet<ApiAppHistory>();
ApiAppHistoryReasonNavigation = new HashSet<ApiAppHistory>();
}
public int Id { get; set; }
public string Name { get; set; }
public string EntityKey { get; set; }
public virtual ICollection<ApiAppHistory> ApiAppHistoryStatusNavigation { get; set; }
public virtual ICollection<ApiAppHistory> ApiAppHistorySentTypeNavigation { get; set; }
public virtual ICollection<ApiAppHistory> ApiAppHistoryReasonNavigation { get; set; }
}
}