Connecting two lists with LINQ?

Question

I'm not sure how best to do this. Just LINQ queries are easy enough for me, but I'm looking for the most efficient way here.

I have 2 tables, sliders and sliders. SliderItems have an FK that points to the id of the sliders.

I get information about a particular slider:

public static List<Slider> GetSlider(Slider sItem) { DBContext db = new DBContext(); if (sItem.Id != Guid.Empty) { var list = from tbl in db.Sliders where tbl.Id == sItem.Id orderby tbl.Id select tbl; return list.ToList(); } } 

So what I need to do for my homepage is to drop the data set that I want to write to the data list. Thus, combining Slider + SliderItems that go with it.

Am I just doing a normal LINQ query with a JOIN statement and throwing them into a shared list that returns to my DataList?

Appreciate any thoughts and direction.

Answer 1

If your database already has a foreign key relationship between Sliders and SliderItems, and if your LINQ-to-SQL model includes both Sliders and SliderItems, then the Slider search will automatically retrieve all the associated SliderItems:

 public static IEnumerable<SliderItems> GetSliderItems(Guid sliderId) { using (DataContext dc = new DataContext()) { Slider result = dc.Sliders.Single(s => s.Id == sliderId); return result.SliderItems; } } 

Answer 2

It seems OK, but you marked your question as ASP.Net. My question to you is: how big is this list and how often do you display this page?

If you show it often and not too big, I would pull the slider table into memory and then run your query without getting into the database.

In fact, if possible, you can cache the entire slider and slider if you can.

Even if you cache the last use, if it makes sense for your application, there is no need to hit DB, it will give you a better gain than optimizing what seems like a very trivial parent / child relationship. I assume you have an index in the database down tbl.Id ?

Answer 3

I suggest you use a Linq query with a join and put the data in a list. This works well for you.