Kixoka Kixoka - 1 month ago 7
C# Question

How to create result set from Linq with enumerable types?

The result set below:

ServiceName Ping Desc LogName BaseUrl EnvName
IntegrationServices.BillingInstructionsService /IntegrationServices/BillingInstructionsService.svc/Rest/Ping BillingInstructionsService IntegrationServices.BillingInstructionsServices https://icrDev.xxx.com Dev
IntegrationServices.BillingInstructionsService /IntegrationServices/BillingInstructionsService.svc/Rest/Ping BillingInstructionsService IntegrationServices.BillingInstructionsServices https://IUTD01.xxx.com DevUnitTest
IntegrationServices.BillingInstructionsService /IntegrationServices/BillingInstructionsService.svc/Rest/Ping BillingInstructionsService IntegrationServices.BillingInstructionsServices https://ickd01.xxx.com DevClock
IntegrationServices.BillingInstructionsService /IntegrationServices/BillingInstructionsService.svc/Rest/Ping BillingInstructionsService IntegrationServices.BillingInstructionsServices https://icd01.xxx.com DevConv


is returned from the linq query below:

var data = contextObj.ServiceMonitorMappings
.Where(r => r.ServiceId == 33)
.Select(x => new
{
ServiceName = x.Service.Name,
Ping = x.Service.PingUrl,
Desc = x.Service.Description,
LogName = x.ServiceLoggingName.LoggingName,
BaseUrl = x.ServiceBaseUrl.ServiceBaseUrl1,
EnvName = x.ServiceEnvironment.Name
});


ServiceMonitorMapping looks like this:

public partial class ServiceMonitorMapping
{
public int Id { get; set; }
public int ServiceEnvironmentId { get; set; }
public int ServiceId { get; set; }
public int ServiceLoggingNameId { get; set; }
public int ServiceBaseUrlId { get; set; }

public virtual Service Service { get; set; }
public virtual ServiceLoggingName ServiceLoggingName { get; set; }
public virtual ServiceBaseUrl ServiceBaseUrl { get; set; }
public virtual ServiceEnvironment ServiceEnvironment { get; set; }
}


I was trying to get BaseUrl and EnvName to return as an enumerable collection so that I wouldn't have 4 records but 1 with the last 2 columns containing a list of BaseUrl and EnvName, however I cannot find a way to do this. So I am stuck with 4 records as opposed to 1. Does not seem to be ideal to me.

So my question is this, is it possible to return just 1 row with the last 2 columns being a collection so that I have "item item item item List<> List<>"?

Any help would be appreciated.

Answer
var data = contextObj.ServiceMonitorMappings
            .Where(r => r.ServiceId == 33)
            .Select(x => new
            {
                Key = new {ServiceName = x.Service.Name,
                           Ping = x.Service.PingUrl,
                           Desc = x.Service.Description,
                           LogName = x.ServiceLoggingName.LoggingName};
                BaseUrl = x.ServiceBaseUrl.ServiceBaseUrl1,
                EnvName = x.ServiceEnvironment.Name
            })
            .GroupBy(x => x.Key)
            .Select(g => new
            {
              ServiceName = g.Key.ServiceName,
              Ping = g.Key.Ping,
              Desc = g.Key.Desc,
              LogName = g.Key.LogName,
              BaseUrls = g.Select(x => x.BaseUrl).ToList(),
              EnvNames = g.Select(x => x.EnvName ).ToList();
            })