Simon Simon - 3 months ago 15
C# Question

Project a Query onto an anonymous Dictionary<string,int>

I am trying to check if an entity in the database has any foreign key relations, so that I can inform the user the entity can or cannot be deleted.

I understand this can be done in a rolled back transaction, however I would like to inform the user how many references and where they are to assist in their decision to delete the entity.

I am trying to avoid loading the entire navigation collection into memory to get this data as it may be large. So, in light of this, I can formulate this simple query to firstly determine if there are any references:

private bool CanDeleteComponent(int compId)
{
var query = _Context.Components.Where(c => c.ComponentID == compId)
.Select(comp => new
{
References = comp.Incidents.Any() &&
comp.Drawings.Any() &&
comp.Documents.Any() &&
comp.Tasks.Any() &&
comp.Images.Any() &&
comp.Instructions.Any()
});
var result = query.FirstOrDefault();
if (result != null)
{
return !result.References;
}
return true;
}


This performs a series of
SELECT COUNT(*) FROM <TABLE> WHERE...
queries.

Now, I would like to provide some further information on the number of references. Ideally I would like to return a Dictionary with the referenced data's name, and the associated count. This way I can loop through the result, rather than access individual properties of an anonymous type. However, what I have tried results in an exception:

var query = _Context.Components.Where(c => c.ComponentID == compId)
.Select(comp => new Dictionary<string, int>
{
{"Events", comp.Incidents.Count()},
{"Drawings", comp.Drawings.Count()},
{"Documents", comp.Documents.Count()},
{"Tasks", comp.Tasks.Count()},
{"Images", comp.Images.Count()},
{"Instructions", comp.Instructions.Count()},
});
var result = query.FirstOrDefault();
return query.Any(fk => fk.Value > 0);


The exception that is raised is:

A first chance exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll
Additional information: Only list initializer items with a single element are supported in LINQ to Entities.


Is there any way around this, such that I can return some sort of IEnumerable rather than an anonymous type?

Thanks

EDIT
I currently have lazy loading disabled on my context. If there is a solution without turning Lazy loading on that would be appreciated.

Answer

You can't build a Dictionary<K,V> in the SELECT statement, that's why you get System.NotSupportedException. You can get the single Component first by query, and build the dictionary in the memory.

var comp = _Context.Components.SingleOrDefault(c => c.ComponentID == compId);
var dict = new Dictionary<string, int>()
{
    { "Events", comp.Incidents.Count()},
    { "Drawings", comp.Drawings.Count()},
    { "Documents", comp.Documents.Count()},
    { "Tasks", comp.Tasks.Count()},
    { "Images", comp.Images.Count()},
    { "Instructions", comp.Instructions.Count()}
};

EDIT If you are not using lazy loading, you can explicitly .Include the properties in the query:

var comp = _Context.Components
    .Include(c => c.Incidents)
    ...
    .SingleOrDefault(c => c.ComponentID == compId);
Comments