Join two tables in Entity Framework version 6.0 Asp.net mvc with comma separated values

Question

I'm stuck in an issue which I'm unable to solve. I have three tables as below.

Number :

ID	Number	User	Comments
1	123	Test User 1	This is a test user 1
2	456	Test User 2	This is a test user 2

Class :

public class NumberViewModel
        {
            [ScaffoldColumn(false)]
            public int ID { get; set; }
            [Required]
            [DisplayName("Number")]
            [StringLength(15, ErrorMessage = "Field length must be 15 digits long.")]
            [RegularExpression(@"^[0-9]+$", ErrorMessage = "Must be number.")]
            public string Number { get; set; }
    
            [DisplayName("User")]
            [StringLength(255, ErrorMessage = "Field maximum length is 255.", MinimumLength = 0)]
            public string User{ get; set; }
            public string ConfigurationMapID { get; set; }
  public string JSON{ get; set; }

    
            [DisplayName("Comments")]
            [StringLength(255, ErrorMessage = "Field maximum length is 255.", MinimumLength = 0)]
            public string Comments { get; set; }
        }
}

Configuration :

Configuration ID	Configuration description	JSON
1	Test 1	{"test1":"test1"}
2	Test 2	{"test2":"test2"}

Class :

public class Configuration :  Entity 
        {
            
            public string ID { get; set; }
            public string Description { get; set; }
            public string Json { get; set; }
    }

Configuration Map :

Map ID	Configuration ID	NumberID
1	1	1
2	2	1

Class :
public class ConfigurationMap:  Entity 
        {
            public int ID { get; set; }
            public string ConfigurationID { get; set; }
            public Nullable<int> NumberID { get; set; }
        }

I need to join these tables to get a result set as below Table :

Number ID	Configuration ID	JSON	Number	User	Comments
1	1,2	{"test1":"test1"} , {"test2":"test2"}	123	Test User 1	This is a test user 1

From a previous method I'm already getting the list from the first table which I don't want to change if possible. So I looped on the first table and updated the values being sent back like this.

var resultData = LoadNumberData(); //This fetches the data from the first table
foreach (var item in resultData){
var target = new GenericRepository<Data.Entities.ConfigurationMap>(ContextConfig).AsNoTracking().Where(c => c.NumberID == item.ID).AsEnumerable()
.Select(x => new{x.ConfigurationID}).ToList();
if (target.Count() > 0){
item.ConfigurationID = String.Join(",", target.Select(x => x.ConfigurationID));}
}

Is there a possibility if i could join the "Configuration" with "ConfigurationMap" table and get JSON field as a comma separated string like i have the ConfigurationID field in the above code and then update the result set of the first table?

Answer 1

Seems simple enough:

var mapRepository = new GenericRepository<Data.Entities.ConfigurationMap>(ContextConfig);
var configRepository = new GenericRepository<Data.Entities.Configuration>(ContextConfig);

var resultData = LoadNumberData();
foreach (var item in resultData)
{
    var configurationIds = mapRepository
        .Where(c => c.NumberID == item.ID)
        .Select(c => c.ConfigurationID)
        .ToList();
    
    if (configurationIds.Count == 0) continue;
    
    item.ConfigurationID = string.Join(",", configurationIds);
    
    var configurations = configRepository
        .Where(c => configurationIds.Contains(c.ID))
        .Select(c => c.Json)
        .ToList();
    
    item.Json = string.Join(",", configurations);
}