CODEWITHSUNDEEP
c#asp.net-mvcrazordatatableviewbag
Andy Donegan
Andy Donegan

Reputation: 915

MVC Merging two foreign tables to one view very slow

I have just managed to load data from one table into another and return this to the view, all working well.

Unfortunately through my learning process and failing to understand other posts relating to returning two models to one view and from using the MVC 5 Tutorial by Rick Anderson

My Controller is taking forever to create my "merged" data table model and return it to the view, also when I manipulate the view it goes through the whole process again which is an ever big kick in the shin.

I have a Van model does not hold the SIM or IMEI number of its GPRS unit this is stored in an Assett table which both are not index linked (if that is the correct wording). I wish to return to the view a full list of Vans along with the IMEI and SIM number from the Assett Table.

Van Model 

[MetadataType(typeof(VanViewModel))]
public partial class Vans__
{
    public string IMEI { get; set; }
    public string SimNo { get; set; }
}

public class VanViewModel
{
    [Required]
    public int AssetID { get; set; }

    [Required]
    [Display(Name = "Chassis Number")]
    public string Van_Serial_Number { get; set; }

    [Required]
    [Display(Name = "Seller Dealer ID")]
    public int Dealer_ID { get; set; }
}

Asset Model

public partial class Asset
{
    public int AssetID { get; set; }
    public string AssetName { get; set; }
    public string SIM { get; set; }
    public string IMEI { get; set; }
}

Controller

public ActionResult Index()
{
    List<Asset> Assets = new List<Asset>();
    List<Vans__> Vans = new List<Vans__>();
    Vans = db.Vans__.ToList();
    foreach (var myVan in Vans)
    {
        int assetIDtoSearch = myVan.AssetID;
        Asset myAsset = dbasset.Assets.Where(m => m.AssetID == assetIDtoSearch).Single();
        myVan.IMEI = myAsset.IMEI;
        myVan.SimNo = myAsset.SIM;
    }
    // Query Asset Table to get IMEI and SIM Number
    return View(db.Vans__.ToList()); 
}

My Controller is the reason for the slow response I understand, I would just appreciate some guidance in relation to my code to help me understand a way to return both models and sort them together in the view. If somebody could take the time to highlight a way to speed up my code or how to get my two models through to the view correctly it would be very appreciated.

From Stephen's suggestion I realised my controller could just return vans to the view. I have implemented this now, but still generating the model like this on request is there a way of making it quicker to return the view.

Here is my updated Controller

        public ActionResult Index()
   {
        // Below code is too slow itterates through tables and populates them very slowly - can not release tbh.
        List<Asset> Assets = new List<Asset>();
        List<Vans__> Vans = new List<Vans__>();

        Vans = db.Vans__.ToList();

        foreach (var myVan in Vans)
        {
            int assetIDtoSearch = myVan.AssetID;
            Asset myAsset = dbasset.Assets.Where(m => m.AssetID == assetIDtoSearch).Single();
            myVan.IMEI = myAsset.IMEI;
            myVan.SimNo = myAsset.SIM;
        }

        return View(Vans); // Do not need to return db.vans__.tolist() as Vans contains this already.
    }

I have amended the title as realised my data tables are on separate data bases. If somebody could please point me in the correct direction to speed up my code or a good example of how to join the two tables so I can access the data in one view it would be appreciated.

Upvotes: 0

Views: 109

Answers (1)

Spluf
Spluf

Reputation: 820

The main reason for slow loading in your case is the loop that connects to the database. From what I see you only need the id for vans, so in your controller, in stead of:

Vans = db.Vans__.ToList();

try using a Linq query or the lamda equivalent as in this following line:

Vans = db.Vans__.Select(p => p.AssetID).ToList();

so now, for the next one, in stead of writing a loop you can pass another query to the database by replacing this loop:

        foreach (var myVan in Vans)
    {
        int assetIDtoSearch = myVan.AssetID;
        Asset myAsset = dbasset.Assets.Where(m => m.AssetID == assetIDtoSearch).Single();
        myVan.IMEI = myAsset.IMEI;
        myVan.SimNo = myAsset.SIM;
    }

with this:

var assets = dbasset.assets.where(m => Vans.Contains(m.AssetID)).ToList();

or

var assets = dbasset.assets.where(m => Vans.Select(p => p.AssetID).Contains(m.AssetID)).ToList();

- one of this two lines should work, so just try both of them in your code and see which one's right:)

and this will give you the list of all those assets. I would suggest you use projection again and just get the fields that you need by using the .Select() as shown before.

Also, take a look at this video: http://youtube.com/watch?v=GmyVMSf4DtE&feature=youtu.be it explains more about optimising your queries and why you should do this.

Upvotes: 0

Related Questions