How to ask Automapper to capture a related record by an internal join in an Id field that is not a foreign key?

Question

I used Automapper for a while, and so far everything is working fine. But recently I came across some "limitations" (or lack of my knowledge). Let me give you a simplified example with two classes:

public class Consumable 
{
    public int ConsumableId { get; set; }
    public string Description { get; set; }
    public int SaleDepartmentId { get; set; }
}

public class SaleDepartment 
{
    public int SaleDepartmentId { get; set; }
    public string Description { get; set; }
}

These two objects store the SaleDepartment identifier, but there is no foreign key linking SaleDepartment to Consumable (and I don't want it to be a key), however SaleDepartment has PrimaryKey on SaleDepartmentId

Now my DTO looks very similar

public class ConsumableDTO 
{
    public int ConsumableId { get; set; }
    public string Description { get; set; }
    public int SaleDepartmentId { get; set; }
}

Here is the mapping

Mapper.CreateMap<Consumable, ConsumableDTO>().ReverseMap();

Therefore, at any time when I bring a collection of ConsumableDTOs, I also want to give the corresponding descriptions of SaleDepartments. If there was a navigation property, I would do something like this

Mapper.Map<ObservableCollection<Consumable>>
(context.Consumable.Project().To<ConsumableDTO>()); 

, , ? , , , , , , automapper,

var foo = new ObservableCollection<Consumable>(
          (from c in context.Consumable.Project().To<ConsumableDTO>()
           join sd in context.SaleDepartment on c.SaleDepartmentId equals sd.SaleDepartmentId
           select new
           {
               consumable = c,
               SaleDepartmentDescription = sd.Description
           }).ToList()
           .Select(p => Mapper.Map<ConsumableDTO, Consumable>(p.consumable, new Consumable() 
           {
               SaleDepartmentDescription = p.SaleDepartmentDescription
           })));

, , , , , , ,

.

Answer 1

-, , DTO public string SaleDepartmentDescription { get; set; }, , .

EF- ( , !), , - EF, , EF, , . ( EF-, , .)

public class Consumable 
{
    public int ConsumableId { get; set; }
    public string Description { get; set; }
    public int SaleDepartmentId { get; set; }
    [ForeignKey("SaleDepartmentId")]
    public virtual SaleDepartment SaleDepartment { get; set; }
}

, DTO SaleDepartmentDescription, AutoMapper , ProjectTo :

var mappedDTOs = context.Consumable.ProjectTo<ConsumableDTO>().ToList();