CODEWITHSUNDEEP
c#asp.net-mvclinqlinq-to-entities
rwkiii
rwkiii

Reputation: 5846

Using Linq to drill down on strongly typed objects

I have been reading tutorials on Linq and Lambda expressions all afternoon. I'll get it eventually and it won't take me that long, but maybe someone can show me an example of how to do what I'm trying for and I'll grasp it quicker.

I have a client side jQuery script that calls a routine in my Controller that returns a JsonResult.

Given ViewModels of:

public class Country
{
    [Key]
    public int Id { get; set; }

    [Index("UX_Country_CountryCode", IsUnique = true)]
    [MaxLength(5)]
    public string CountryCode { get; set; }

    public string CountryName { get; set; }

    public virtual ICollection<State> States { get; set; }
}

public class State
{
    [Key]
    public int Id { get; set; }

    [Index("UX_State_StateCodeCountryId", IsUnique = true, Order = 1)]
    [MaxLength(5)]
    public string StateCode { get; set; }

    public string StateName { get; set; }

    [ForeignKey("Country")]
    [Index("UX_State_StateCodeCountryId", IsUnique = true, Order = 0)]
    public int CountryId { get; set; }

    public virtual Country Country { get; set; }
}

Example data:

new Country { Id = 1, CountryCode = "USA", CountryName = "United States" };
new Country { Id = 2, CountryCode = "CAN", CountryName = "Canada" };

new State { Id = 1, StateCode = "FL", StateName = "Florida", CountryId = 1 };
new State { Id = 2, StateCode = "CA", StateName = "California", CountryId = 1 };
new State { Id = 3, StateCode = "IA", StateName = "Iowa", CountryId = 1 };

Call the following with a URI of /Controller/StateList/USA

[AllowAnonymous]
[AcceptVerbs(HttpVerbs.Get)]
public JsonResult StateList(string Id)
{
    // I need to know the Id of CountryCode == "USA"
    // Get <Country> with CountryCode == "USA". (I think this works)
    IEnumerable<SelectListItem> country = from c in GetCountryList()
                   where c.Value == Id
                   select c;

    // Now get all states where <State>.CountryId == country.Id
    var state = from State s in dbLocations.States
                   where s.CountryId == country.id
                   select s;

    return Json(new SelectList(state.ToArray(), "StateCode", "StateName"), JsonRequestBehavior.AllowGet);
}


public IEnumerable<SelectListItem> GetCountryList()
{
    var countries = new SelectList(dbLocations.Countries, "CountryCode", "CountryName").ToList();
    countries.Insert(0, (new SelectListItem { Text = "Select Country", Value = "-1" }));
    countries.Insert(1, (new SelectListItem { Text = "United Sates", Value = "USA" }));
    countries.Insert(2, (new SelectListItem { Text = "Canada", Value = "CAN" }));
    countries.Insert(3, (new SelectListItem { Text = "Mexico", Value = "MEX" }));
    countries.Insert(4, (new SelectListItem { Text = "Brazil", Value = "BRA" }));
    countries.Insert(5, (new SelectListItem { Text = "------------------------", Value = "-1" }));

    IEnumerable<SelectListItem> countrylist =
        countries.Select(m => new SelectListItem()
        {
           Text = m.Text,
           Value = m.Value
        });

    return countrylist;
}

I've made several attempts at the StateList() code and just not grasping how to do this using Linq. I think GetCountryList() is ok - I use it many times elsewhere in the app. How would StateList() best be coded?

Also, I've found some Linq tutorials, but they're not clicking. Anyone know of a good one?

Upvotes: 1

Views: 579

Answers (1)

Adrian Hristov
Adrian Hristov

Reputation: 2037

The simplest way would be to access dbLocations and fetch the country from the database directly.

[AllowAnonymous]
[AcceptVerbs(HttpVerbs.Get)]
public JsonResult StateList(string Id)
{
    // Get the country by Country Code
    var country = dbLocations.Countries.FirstOrDefault(c => c.CountryCode == Id);

    // Get all states where countryId equals the Id from the action
    var states = from State s in dbLocations.States
               where s.CountryId == country.Id
               select s;

    return Json(new SelectList(states.ToArray(), "StateCode", "StateName"), JsonRequestBehavior.AllowGet);
}

EDIT: You may think of switching the Value of SelectedListItem to the Country.Id instead of Country.CountryCode the value is not seen in the UI anyway. This way it would be easier to access the country by its Id instead of CountryCode

Upvotes: 2

Related Questions