Unable to retrieve data from reference table from Entity Framework

Question

I have three tables, joborder, joborder items and workorder.

joborder is the parent and joborderitem is the child table. workorder has a joborder item id as foreign key to the joborder item table.

I need to get joborderitems where their joborderid's is not present in workorder. workorder will create based on jobordrritem .. so please guide me how get those items ?

Model classes:

public JobOrder()
{
    JobOrderGuid = Guid.NewGuid();
    Weight = 0;
}

public Guid JobOrderGuid { get; set; }
public string JobOrderNumber { get; set; }
public int OrderStatusId { get; set; }
public bool IsDeleted { get; set; }
public int? UQCId { get; set; }  // Future  purpose
public decimal? Weight { get; set; } // Future  purpose

public virtual IList<JobOrderItem> JobOrderItems { get; set; }

#region Custom properties

/// <summary>
/// Gets or sets the order status
/// </summary>
public OrderStatus OrderStatus
{
    get => (OrderStatus)OrderStatusId;
    set => OrderStatusId = (int)value;
}
        
#endregion


public class JobOrderItem : BaseEntity
{
    public int JobOrderId { get; set; }
    public int SNo { get; set; }
    public int ProductId { get; set; }
    public decimal OrderQuantity { get; set; }
    public decimal ReceivedQuantity { get; set; }
    public decimal? ItemWeight { get; set; }
    public bool IsReceived { get; set; }
    public string Details { get; set; }

    public virtual JobOrder JobOrder { get; set; }
    public virtual Product Product { get; set; }
}

public class WorkOrder : BaseEntity
{
    public WorkOrder()
    {
        WorkOrderGuid = Guid.NewGuid();
    }

    public Guid WorkOrderGuid { get; set; }
    public string WorkOrderNumber { get; set; }
    public int JobOrderItemId { get; set; }  // REFERENCE ID 
    public string Details { get; set; }     
    public DateTime WorkStartDate { get; set; }
    public DateTime? WorkCompletedDate { get; set; }

    public virtual JobOrderItem JobOrderItem { get; set; }
    public virtual IList<WorkOrderStateInfo> WorkOrderStateInfo { get; set; }
    public virtual IList<WorkOrderItem> WorkOrderItems { get; set; }
}

QUERY :

var result = _context.JobOrders
                     .Include(x => x.JobOrderItems) .....??

Please guide me how to return list of orderitems that are not used in any workorder .. thanks in advance

If you can't upgrade to EF-core 5 then you need a work-around, f/e https://stackoverflow.com/a/16801205/861716 — Gert Arnold, Sep 15 '21 at 07:06
@GertArnold, filtered Include has nothing with this question. — Svyatoslav Danyliv, Sep 15 '21 at 15:53
@SvyatoslavDanyliv Not sure. The question isn't 100% clear on that. — Gert Arnold, Sep 15 '21 at 18:27
@GertArnold, for querying items you do not have to use `Include`. Such query is easily writable by just LINQ. — Svyatoslav Danyliv, Sep 15 '21 at 18:40
@SvyatoslavDanyliv Yes, but that's as simple as `context.JobOrderItems.Where(...)`, not sure what makes OP post the query at the bottom. Hopefully this gives a nudge to enlighten us. — Gert Arnold, Sep 15 '21 at 18:46
Add a navigation property `JobOrderItem.WorkOrders` and check where it's empty (`!Any()`). — Gert Arnold, Sep 16 '21 at 08:42

score 0 · Answer 1 · answered Sep 15 '21 at 19:34

Include is not for querying data but for loading related data. Simple LINQ query with LEFT JOIN can solve your problem.

var withoutWorkOrder = 
    from ji in context.JobOrderItems
    join wo in context.WorkOrders on ji.Id equals wo.JobOrderItemId into gj
    from wo in gj.DefaultIfEmpty()
    where (int?)wo.Id == null
    select ji;

Unable to retrieve data from reference table from Entity Framework

1 Answers1