CODEWITHSUNDEEP
linq-to-sql
emaz
emaz

Reputation: 1

Linq to sql: Merge child rows into one single entity

I have three tables like so:

Presets:

ID int PK,
Name nvarchar

Preset_milestone_presets: (link table)

presetId int PK, FK to Preset-table.
milestoneId int PK

Preset_titles_presets: (link table)

presetId int PK, FK to Preset-table.
titleId int PK

And a C# Entity like so:

Preset:

int[] milestoneIds
int[] titleIds

So I'm wondering what the best way, using linq-to-sql, is to create on single entity that have a merged distinct list of milestoneIds and titleIds that represents multiple presets. (Joined against multiple rows in the Presets table)?

EDIT: Data examples:

Prestes

1   "Preset 1"
2   "Preset 2"

Preset_milestone_presets

1   1
1   2
1   3
1   4
2   1
2   5
2   6

Preset_titles_presets

1   1
1   2
2   1
2   3
2   4

Expected result when requesting presets with id 1 and 2 is a single entity with the following arrays: milestoneIds: { 1, 2, 3, 4, 5, 6 } titleIds: { 1, 2, 3, 4 }

Upvotes: 0

Views: 442

Answers (1)

dani herrera
dani herrera

Reputation: 51715

I hope that this code help to you:

Dictionary<string, List<int>> result = 
   new Dictionary<string, List<int>>();

/*id's */
List<int> ids = new List<int>(new int[] { 1,2 });

/* Tanking Prestes */
var my_presets = 
   (from p in Prestes 
    join i in ids
    on p.id = i
    select p);

/*Taking Milestones*/
my_milestones_ids = 
   (from m in Preset_milestone_presets 
    join p in my_presets
    on m.idPreset = p.id
    select m.id).Distinct().ToList();

/*Taking titles*/
my_titles_ids = 
   (from t in Preset_titles_presets 
    join p in my_presets
    on t.idPreset = p.id
    select t.id).Distinct().ToList();

/* result */
result.Add( "presets_ids", ids);
result.Add( "milestones_ids", my_milestones_ids);
result.Add( "titles_ids", my_titles_ids);

If you don't like joins try with:

/* Tanking Prestes */
var my_presets = 
   (from p in Prestes 
    where ids.Contains( p.id)
    select p);

/*Taking Milestones*/
my_milestones_ids = 
   (from m in Preset_milestone_presets 
    where my_presets.Contains( m.Preset) 
    select m.id).Distinct().ToList();

/*Taking titles*/
my_titles_ids = 
   (from t in Preset_titles_presets 
    where my_presets.Contains( t.Preset) 
    select t.id).Distinct().ToList();

Upvotes: 1

Related Questions