locked
Having Problem with Ajax call - Entity Framework MVC Joining 2 Tables RRS feed

  • Question

  • User1912965948 posted

    Hi all,

    I hope you guys must be doing well in your respective domain.

    I'm facing problem to call ajax. I want retrieve data from sql to jquery datatable, without joining tables everything working fine

    when I join tables it dose not work kindly help me out.

    Sql Tables are as follow

    1. Customer
    CusId CusName PhoneNo FaxNo WebURL
    1 ABC 12121212 212121 abc.com

    2. Address

    AddId Address CusId
    1 Industrial Area 1

    HTML

    <table id="customerTble">
        <thead>
            <tr>
                <th>Customer Name</th>
                <th>Phone No</th>
                <th>Fax No</th>
                <th>Web URL</th>
                <th>Address</th>
            </tr>
        </thead>
    </table>

    Script

    <script type="text/javascript">
            jQuery(document).ready(function () {
                debugger;
                jQuery('#customerTble').DataTable({
                    'ajax': {
                        'url': '@Url.Action("GetCustomerList", "Customer")',
                        'type': 'GET',
                        'dataType': 'JSON'
                    },
                    'columns': [
                        { "data": "CusName" },
                        { "data": "PhoneNo" },
                        { "data": "FaxNo" },
                        { "data": "WebURL" },
                        { "data": "Address" }
                    ]
                });
            });
        </script>

    Controller

    public ActionResult GetCustomerList()
            {
                DBEntities db = new DBtEntities();
                List<Customer> customerData = db.Customers.ToList();
                List<Address> addressData = db.Addresses.ToList();
    
                var data = from c in customerData
                           join a in addressData on c.CusId equals a.CusId
                           select new CustomerDetails{ customer = c, address = a };
                return Json(new { data = data }, JsonRequestBehavior.AllowGet);
            }

    Model

    public partial class Customer
        {
            public int CusId { get; set; }
            public string CusName { get; set; }
            public string WebURL { get; set; }
            public string PhoneNo { get; set; }
            public string FaxNo { get; set; }
        }
    public partial class Address
        {
            public int AddId { get; set; }
            public string Address1 { get; set; }
            public Nullable<int> CusId { get; set; }
        }
    public class CustomerDetails
        {
            public Customer customer { get; set; }
            public Address address { get; set; }
        }

    I'll appreciate if someone get me out from this problem.

    Thx in advance

     

    Tuesday, December 3, 2019 6:26 AM

Answers

  • User-1780421697 posted

    First of all you need to get the result from calling action method , it will return result in different format what you are expecting in front end jQuery('#customerTble').DataTable, so you can directly check the result from PostMan tool or you can get the result in success function of GET request, that will show you actual shape of data.

    You have possibly two options to solve data formatting issue, either you can return single object from LINQ query or you can transform data on client side.i think you need to transform the format  of data in LINQ and return result as

       object { CusName,PhoneNo,FaxNo,WebUrl,Address}
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 3, 2019 7:19 AM
  • User-1780421697 posted
    using System;
    using System.Linq;
    using System.Collections.Generic;
    
    public class Simple {
      public static void Main() 
      {
     
    	   List<Customer> Customers = new List<Customer>{new Customer{ CusId=1,CusName="C1",PhoneNo="123", FaxNo="1111", WebURL="test.com"   }, new Customer{CusId=2, CusName="C2", PhoneNo="453", FaxNo="2222", WebURL="test2.com" }};
    	   List<Address> Addresses = new List<Address>{new Address{ CusId=1,Address1="A-1"},new Address{ CusId=2,Address1="A-2"}};	  
    	  
    	   var result = from c in Customers
    		            join a in Addresses on c.CusId equals a.CusId
    		            select new { CusName = c.CusName,PhoneNo = c.PhoneNo,FaxNo = c.FaxNo,WebUrl= c.WebURL,Address = a.Address1, WebURL= c.WebURL };
    	  
         foreach(var item in result)
    	 {
    	   Console.WriteLine("Name: "+item.CusName +"  - PhoneNo:"+item.PhoneNo+"  - FaxNo:"+item.FaxNo +"  - WebUrl:"+item.WebURL+"  - Address:"+item.Address);
    	 }
      }
    	
    	public class Customer
        {
            public int CusId { get; set; }
            public string CusName { get; set; }
            public string WebURL { get; set; }
            public string PhoneNo { get; set; }
            public string FaxNo { get; set; }
        }
    	
    	public class Address
        {
            public int AddId { get; set; }
            public string Address1 { get; set; }
            public Nullable<int> CusId { get; set; }
        }
    }
    
    
    RESULT:
    Name: C1  - PhoneNo:123  - FaxNo:1111  - WebUrl:test.com  - Address:A-1
    Name: C2  - PhoneNo:453  - FaxNo:2222  - WebUrl:test2.com  - Address:A-2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 3, 2019 7:31 AM

All replies

  • User-1780421697 posted

    First of all you need to get the result from calling action method , it will return result in different format what you are expecting in front end jQuery('#customerTble').DataTable, so you can directly check the result from PostMan tool or you can get the result in success function of GET request, that will show you actual shape of data.

    You have possibly two options to solve data formatting issue, either you can return single object from LINQ query or you can transform data on client side.i think you need to transform the format  of data in LINQ and return result as

       object { CusName,PhoneNo,FaxNo,WebUrl,Address}
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 3, 2019 7:19 AM
  • User1912965948 posted

    Thank you khuram,

    Would you mind to write a little code it will be easy to understand for me.

    Regards

    Tuesday, December 3, 2019 7:29 AM
  • User-1780421697 posted
    using System;
    using System.Linq;
    using System.Collections.Generic;
    
    public class Simple {
      public static void Main() 
      {
     
    	   List<Customer> Customers = new List<Customer>{new Customer{ CusId=1,CusName="C1",PhoneNo="123", FaxNo="1111", WebURL="test.com"   }, new Customer{CusId=2, CusName="C2", PhoneNo="453", FaxNo="2222", WebURL="test2.com" }};
    	   List<Address> Addresses = new List<Address>{new Address{ CusId=1,Address1="A-1"},new Address{ CusId=2,Address1="A-2"}};	  
    	  
    	   var result = from c in Customers
    		            join a in Addresses on c.CusId equals a.CusId
    		            select new { CusName = c.CusName,PhoneNo = c.PhoneNo,FaxNo = c.FaxNo,WebUrl= c.WebURL,Address = a.Address1, WebURL= c.WebURL };
    	  
         foreach(var item in result)
    	 {
    	   Console.WriteLine("Name: "+item.CusName +"  - PhoneNo:"+item.PhoneNo+"  - FaxNo:"+item.FaxNo +"  - WebUrl:"+item.WebURL+"  - Address:"+item.Address);
    	 }
      }
    	
    	public class Customer
        {
            public int CusId { get; set; }
            public string CusName { get; set; }
            public string WebURL { get; set; }
            public string PhoneNo { get; set; }
            public string FaxNo { get; set; }
        }
    	
    	public class Address
        {
            public int AddId { get; set; }
            public string Address1 { get; set; }
            public Nullable<int> CusId { get; set; }
        }
    }
    
    
    RESULT:
    Name: C1  - PhoneNo:123  - FaxNo:1111  - WebUrl:test.com  - Address:A-1
    Name: C2  - PhoneNo:453  - FaxNo:2222  - WebUrl:test2.com  - Address:A-2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 3, 2019 7:31 AM