Only returning one record in dataset

I am using ado .net in my web API and dapper and I have a function that checks the orders and the order lines it works fine if their only one order but if there an order without order lines it won’t display the order details.

public IHttpActionResult Get()
{
        string retJson;
        string constring = ConfigurationManager.AppSettings["DeliveryGocs"].ToString();
        string sql = "SELECT * FROM Deliverys AS A INNER JOIN DeliveryLines AS B ON A.id = B.DeliveryId;";
        using (var connection = new SqlConnection(constring))
        {
            var orderDictionary = new Dictionary<int, DeliverysItems>();
            var list = connection.Query<DeliverysItems, DeliverItemLines, DeliverysItems>(
                sql,
                (order, orderDetail) =>
                {
                    DeliverysItems orderEntry;
                    if (!orderDictionary.TryGetValue(order.id, out derEntry))
                    {
                        orderEntry = order;
                        orderEntry.DeliveryLines = new List<DeliverItemLines>();
                        orderDictionary.Add(orderEntry.id, orderEntry);
                    }

                    orderEntry.DeliveryLines.ad(orderDetail);
                    return orderEntry;
                })
            .Distinct()
            .ToList();
            retJson = JsonConvert.SerializeObject(list);
            var response = this.Request.CreateResponse(HttpStatusCode.OK);
            response.Content = new StringContent(retJson, Encoding.UTF8, "application/json");

            return ResponseMessage(response);
        }
    }

Class For Delivery Items

public class DeliverysItems
{
   [Key]
    public int id { get; set; }
    public string SopOrderNumber { get; set; }
    public string CustomerName { get; set; }
    public int DeliveryDriverId { get; set; }
    public decimal OrderTotal { get; set; }
    public string TelephoneNumber    { get; set; }
    public string EmailAddress { get; set; }        
    public int hasBeenDelivered { get; set; }
    public List<DeliverItemLines> DeliveryLines { get; set; }
}

Class for Delivery Lines.

public   class DeliverysItems
{
   [Key]
    public int id { get; set; }
    public string SopOrderNumber { get; set; }
    public string CustomerName { get; set; }
    public int DeliveryDriverId { get; set; }
    public decimal OrderTotal { get; set; }
    public string TelephoneNumber    { get; set; }
    public string EmailAddress { get; set; }

    public int hasBeenDelivered { get; set; }

    public List<DeliverItemLines> DeliveryLines { get; set; }
 }
}

I need the above routine to work in a way that if the order has no order lines it will still display the top order header details. I presume I am missing something in my sql as this should be easy.

For at the min my app is showing the following.

When it should be showing the two records that is here in the data.

Answer

The problem is in your SQL. An INNER JOIN requires a match. In this case that match is between Deliverys.id and DeliveryLines.DeliveryId according to your ON clause.

Use a LEFT JOIN if you want to include deliveries in your results even if a delivery has no delivery lines. Also, be prepared for your delivery lines fields to come back NULL, which is what to expect for those columns when no row exists in the LEFT JOIN match attempt.

Advertisement

Answer