Linq select subquery

Question

As the title states, I'm trying to perform a select subquery in Linq-To-SQL. Here's my situation:

I have a database view which returns the following fields:

• SourceId
• LicenseId
• LicenseName
• CharacteristicId
• CharacteristicName

Now I want to be able to store this in a model of mine which has the following properties

• Id
• Name
• Characteristics (this is List which has Id, Name and Icon => Icon is byte[])

Here's the query I wrote which doesn't work:

var licensesWithCharacteristics =
                _vwAllLicensesWithAttributesAndSourceIdRepository.GetAll()
                .Where(x => x.SourceID == sourceId)
                .Select(a => new LicenseWithCharacteristicsModel()
                {
                    LicenseId = a.LicenseId,
                    LicenseName = a.LicenseName
                        ,CharacteristicList = _vwAllLicensesWithAttributesAndSourceIdRepository.GetAll()
                     .Where(x => x.LicenseId == a.LicenseId)
                     .Select(c => new CharacteristicModel { Id = c.CharacteristicID, Name = c.CharacteristicName, Icon = c.Icon })
                     .Distinct().ToList()
                })
                .Distinct().ToList();

How would you solve this? I'm trying to do this in one query to keep my performance up, but I'm kind of stuck.

Answer 1

Your sample query and models are not that coherent (where does Icon come from, Characteristics or CharacteristicList), but anyway.

I do this in two parts, you can of course regroup this in one query. I enumerate the result after the grouping, you may try to do without enumerating (all in linq to sql, but not sure it will work).

var groupedResult = 
     _vwAllLicensesWithAttributesAndSourceIdRepository.GetAll()
         .Where(x => x.SourceID == sourceId)
         .GroupBy(m => new {m.LicenseId, m.LicenseName})
         .ToList();

var results = groupedResult.Select(group => new LicenseWithCharacteristicsModel {
                  LicenseId = group.Key.LicenseId,
                  LicenseName = group.Key.LicenseName,
                  Characteristics = group.Select(m=> new CharacteristicModel {
                       Id = m.CharacteristicId,
                       Name = m.CharacteristicName
                      }).ToList()
                  });

in "single query"

_vwAllLicensesWithAttributesAndSourceIdRepository.GetAll()
    .Where(x => x.SourceID == sourceId)
    .GroupBy(m => new {m.LicenseId, m.LicenseName})
    .Select(group =>
        new LicenseWithCharacteristicsModel
        {
            LicenseId = group.Key.LicenseId,
            LicenseName = group.Key.LicenseName,
            Characteristics = group.Select(m =>
                new CharacteristicModel
                {
                    Id = m.CharacteristicId,
                    Name = m.CharacteristicName
                }).ToList()
        });