How to perform a LINQ join on IQueryable in oda

问题:

I have a WebAPI method which uses the OData Query Options to return data to the client. The entity has cityid and I want cityname from another entity using joins.

I have tried using below Api method, which is incomplete.

Entity1:-

 public partial class UU_DeliveryCharges
    {
        public int DeliveryChargeId { get; set; }
        public Nullable<int> CityId { get; set; }
        public Nullable<int> VehicleTypeId { get; set; }
        public Nullable<decimal> MileRate { get; set; }
        public Nullable<decimal> FlatRate { get; set; }
        public Nullable<decimal> FlatMile { get; set; }
        public Nullable<decimal> PickUpFee { get; set; }
        public Nullable<decimal> DropOffFee { get; set; }
        public Nullable<int> CreateBy { get; set; }
        public Nullable<System.DateTime> SysDate { get; set; }
        public Nullable<bool> Status { get; set; }
    }

Entity2:-

 public partial class SC_Cities
    {
        public int CityId { get; set; }
        public Nullable<int> StateId { get; set; }
        public string CityName { get; set; }
        public Nullable<bool> CityStatus { get; set; }
    }

WebApi Method:-

    public IQueryable<UU_DeliveryCharges> GetUU_DeliveryCharges()
            {
                //var aaa= db.UU_DeliveryCharges;
                //return aaa;

                var results = (from deliveries in db.UU_DeliveryCharges
                                     join vehicles in db.UU_VehicleTypes on deliveries.VehicleTypeId equals vehicles.VehicleTypeId
                                     join cities in db.SC_Cities on deliveries.CityId equals cities.CityId
                                     join states in db.SC_States on cities.StateId equals states.StateId
                                     join countries in db.SC_Countries on states.CountryId equals countries.CountryId
                                     where (deliveries.Status == true)
                                     select new { deliveries = deliveries.FlatMile, deliveries.MileRate, deliveries.PickUpFee, deliveries.DropOffFee,
                                       vehicles=vehicles.VehicleType, cities = cities.CityName, states = states.StateName, countries = countries.CountryName
                                     }).ToList();
           return results;
         }

Also tried by creating a new class as given below but not getting the right way, TIA.

 public partial class DeliveryCharges
    {
        public string ReturnCode { get; set; } //-1:Error/0:missing or validation /1:success
        public string ReturnMessage { get; set; }
        public string CountryName { get; set; }
        public string StateName { get; set; }
        public string CityName { get; set; }
        public string VehicleName { get; set; }
        public Nullable<decimal> MileRate { get; set; }
        public Nullable<decimal> FlatRate { get; set; }
        public Nullable<decimal> FlatMile { get; set; }
        public Nullable<decimal> PickUpFee { get; set; }
        public Nullable<decimal> DropOffFee { get; set; }
    }

Edit:-

I have replaced Api method by below method, which in turn evaluate the joins but not returning as a result.

public IQueryable<DeliveryCharges> GetUU_DeliveryCharges()
        {
            //var aaa= db.UU_DeliveryCharges;
            //return aaa;
            var results = from deliveries in db.UU_DeliveryCharges
                          join vehicles in db.UU_VehicleTypes on deliveries.VehicleTypeId equals vehicles.VehicleTypeId
                          join cities in db.SC_Cities on deliveries.CityId equals cities.CityId
                          join states in db.SC_States on cities.StateId equals states.StateId
                          join countries in db.SC_Countries on states.CountryId equals countries.CountryId
                          where (deliveries.Status == true)
                          select new DeliveryCharges
                          {
                              FlatRate = deliveries.FlatRate,
                              MileRate = deliveries.MileRate,
                              PickUpFee = deliveries.PickUpFee,
                              DropOffFee = deliveries.DropOffFee,
                              CityName = cities.CityName
                              //UU_DeliveryCharges = deliveries.FlatMile, deliveries.MileRate, deliveries.PickUpFee, deliveries.DropOffFee
                              //,vehicles = vehicles.VehicleType, cities = cities.CityName, states = states.StateName, countries = countries.CountryName
                          };
return results;
}

WebApiConfig :-

   public static void Register(HttpConfiguration config)
        {
     ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntitySet<UU_DeliveryCharges>("UU_DeliveryCharges");
            config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());
}