LINQ query grouping without duplication of data
So I want to display an output that is group by two fields: SubsidiaryCode and AssetCreatedDate. My problem is it displays the grouping values redundantly.
I suspect it duplicates because of my Detail class.
What I want is:
But it displays like this:
LINQ query:
public DateTime FromDate { get; set; }
public DateTime ToDate { get; set; }
public IList<AssetListTemplate> List = new List<AssetListTemplate>();
public IList<AssetListTemplate> GetList()
{
using (var ctx = LinqExtensions.GetDataContext<NXpert.FixedAsset.DataAccess.FixedAssetDataContext>("AccountingDB"))
{
var list = (from x in ctx.DataContext.AssetRegistryEntities
where x.SubsidiaryCode2 != "" && x.SubsidiaryCode2.ToUpper().Contains("y-") && x.AssetCreatedDate>=FromDate && x.AssetCreatedDate <= ToDate
group new { x.SubsidiaryCode2, x.AssetCreatedDate,x.AssetCategoryID } by x into groupedList
select new AssetListTemplate
{
IsSelected = false,
SubsidiaryCode = groupedList.Key.SubsidiaryCode2,
AssetCreatedDate = groupedList.Key.AssetCreatedDate,
AssetCategory = groupedList.Key.AssetCategoryID
}
).OrderBy(x => x.SubsidiaryCode).ThenBy(y => y.AssetCreatedDate).ToList();
List = list;
foreach (var item in List)
{
var details = (from x in ctx.DataContext.AssetRegistryEntities
join y in ctx.DataContext.AssetCategoryEntities on x.AssetCategoryID equals y.AssetCategoryID
join z in ctx.DataContext.FixedAssetOtherInfoEntities on x.AssetCode equals z.AssetCode
where x.SubsidiaryCode2 == item.SubsidiaryCode
select new Details
{
AssetCode = x.AssetCode,
AssetCodeDesc = y.AssetCategoryDesc,
AssetDesc = x.AssetCodeDesc,
DepInCharge = z.DepartmentInCharge,
SerialNo = x.SerialNumber,
ModelNo = x.ModelNumber
}).ToList();
item.Details = details;
}
return List;
}
}
}
public class AssetListTemplate
{
public bool IsSelected { get; set; }
public string SubsidiaryCode { get; set; }
public DateTime? AssetCreatedDate { get; set; }
public string AssetCategory { get; set; }
public List<Details> Details { get; set; }
}
public class Details {
public string AssetCode { get; set; }
public string AssetCodeDesc { get; set; }
public string AssetDesc { get; set; }
public string DepInCharge { get; set; }
public string SerialNo { get; set; }
public string ModelNo { get; set; }
}
SQL Query:
SELECT Are_SubsidiaryCode2[SubsidiaryCode],Are_AssetCreatedDate[AssetCreatedDate],Are_AssetCategoryID[AssetCategory]
FROM E_AssetRegistry
WHERE Are_SubsidiaryCode2<>''
AND Are_SubsidiaryCode2 LIKE '%Y-%'
GROUP BY Are_SubsidiaryCode2
,Are_AssetCreatedDate
,Are_AssetCategoryID
ORDER BY AssetCreatedDate ASC
You don't seem to be using the grouping for any aggregate function , so you could make life simpler by just using distinct:
from x in ctx.DataContext.AssetRegistryEntities
where x.SubsidiaryCode2.Contains("y-") && x.AssetCreatedDate>=FromDate && x.AssetCreatedDate <= ToDate
select new AssetListTemplate
{
IsSelected = false,
SubsidiaryCode = groupedList.Key.SubsidiaryCode2,
AssetCreatedDate = groupedList.Key.AssetCreatedDate.Value.Date,
AssetCategory = groupedList.Key.AssetCategoryID
}
).Distinct().OrderBy(x => x.SubsidiaryCode).ThenBy(y => y.AssetCreatedDate).ToList();
Side note, you don't need to assign a list to a clas variable and also return it; I'd recommend just to return it. If you're looking to cache the results, make the class level var private, assign it and return it first time and just return it the second time (use the null-ness of the class level var to determine if the query has been run)
Expanding on the comment:
You don't need to store your data in a public property and also return it. Don't do this:
public class Whatever{
public string Name {get;set;}
public string GetName(){
var name = "John";
Name = name;
return name;
}
Typically we would either return it:
public class Whatever{
public string GetName(){
var name = MySlowDatabaseCallToCalculateAName();
return name;
}
//use it like:
var w = new Whatever();
var name = w.GetName();
Or we would store it:
public class Whatever{
public string Name {get;set;}
public void PopulateName(){
Name = MySlowDatabaseCallToCalculateAName();
}
//use it like
var w = new Whatever();
w.PopulateName();
var name = w.Name;
We might have something like a mix of the two if we were providing some sort of cache, like if the query is really slow and the data doesn't change often, but it is used a lot:
public class Whatever{
private string _name;
private DateTime _nameGeneratedAt = DateTime.MinValue;
public string GetName(){
//if it was more than a day since we generated the name, generate a new one
if(_nameGeneratedAt < DateTime.UtcNow.AddDays(-1)){
_name = MySlowDatabaseCallToCalculateAName();
_nameGeneratedAt = DateTime.UtcNow; //and don't do it again for at least a day
}
return _name;
}
This would mean that we only have to do the slow thing once a day, but generally in a method like "Get me a name/asset list/whatever" we wouldn't set a public property as well as return the thing; it's confusing for callers which one to use - they want the name; should they access the Name property or call GetName? If the property was called "MostRecentlyGeneratedName" and the method called "GenerateLatestName" it would make more sense - the caller can know they might call Generate..( first, and then they could use MostRecently.. - it's like a caching; the calling class can decide whether to get the latest, or reuse a recently generated one (but it does introduce the small headache of what happens if some other operation does a generate in the middle of the first operation using the property..)..
..but we probably wouldn't do this; instead we'd just provide the Generate..( method and if the caller wants to cache it and reuse the result, it can