How to loop through the Items below and update the Field which have empty values with the value (!) Error:
Cannot implicitly convert type System.threading.task to System.Collections.Generic.IEnumerable<Respositories.AssignmentMasterData>
using (SUPEntities db = new SUPEntities())
{
IEnumerable<AssignementMasterData> masterDatas = null;
masterDatas = db.AssignementMasterDatas
.Where(m => DbFunctions.TruncateTime(m.CreatedDateTime) >= DbFunctions.TruncateTime(criteria.FilterStartDate)
&& DbFunctions.TruncateTime(m.CreatedDateTime) <= DbFunctions.TruncateTime(criteria.FilterEndDate)
&& (m.AssignmentNoteNumber == criteria.AssigmentNumber || criteria.AssignmentNumber == null)
&& (m.BaseCourseId == criteria.courseId || criteria.CourseId == 0)
&& (m.AccountNumber == criteria.AccountNumber || criteria.AccountNumber == null)
&& (m.ReferenceNumber == criteria.ReferenceNumber || criteria.ReferenceNumber == null)
&& (m.FacultyCode == criteria.FAcultyCode || criteria.FacultyCode == null)
&& (m.Processed == criteria.Processed)
&& (m.ClassNumber == criteria.ClassNumber || criteria.ClassNumber == null))
.ForEachAsync(t => t.AssignmentNoteIdentifiedClasses.Select(e => String.IsNullOrEmpty(e.Category)? "(!)": e.Category));
}
CodePudding user response:
- Use
.Include( m => m.AssignmentNoteIdentifiedClasses )
to bring-in related data in a single query, this is much faster than loading each set of relatedAssignmentNoteIdentifiedClasses
in your for-each-row loop. - You don't need to use
TruncateTime
.- In fact, you shouldn't because that will mean your query isn't SARGable.
- Avoid functions in SQL predicates.
- Instead, just round
criteria.FilterStartDate
down to the start-of-day in application code and compare it normally withm => m.CreatedDateTime >= filterStart
. - Similarly,
FilterEndDate
should be rounded-up and then compared like so:m => m.CreatedDateTime < filterEnd
- Always use exclusive upper-bounds. It makes everything, especially date-range predicates, much easier to deal with.
- You don't need inline
&&
in yourWhere
. Use additional separate.Where()
clauses instead. They'll be added to the same (single)WHERE
clause as separateAND
terms. - I assume EF Core isn't sophisticated enough to recognize the "
NULL
-means-ignore" anti-pattern for optional search predicates, in which case DON'T USE the "NULL
-means-ignore" IN ANIQUERYABLE<T>
PREDICATE!- This is bad for so many reasons: namely because query execution-plans are based on the structure ("shape") of the SQL query and not on parameter values, so the same cached execution-plan for non-
NULL
parameters will be used when some, or even all parameters areNULL
- which is a problem. - Instead build your query by using
IQueryable<T>
's Linq extensions and reassigning to itself.- e.g.
IQueryable<T> query = db.Etc; query = query.Where( e => etc );
- Each
.Where()
is added as anAND
condition. If you want to build-up anOR
condition then usePredicateBuilder
.
- e.g.
- This is bad for so many reasons: namely because query execution-plans are based on the structure ("shape") of the SQL query and not on parameter values, so the same cached execution-plan for non-
DateTime filterStart = criteria.FilterStartDate.Date;
DateTime filterEndExcl = criteria.FilterEndDate .Date.AddDays(1);
using (SUPEntities db = new SUPEntities())
{
IQueryable<AssignementMasterData> query = db.AssignementMasterDatas
.Include( m => m.AssignmentNoteIdentifiedClasses )
.Where( m => m.CreatedDateTime >= filterStart )
.Where( m => m.CreatedDateTime < filterEndExcl ) // Exclusive upper-bound.
.Where( m => m.Processed == criteria.Processed )
.Where( m => m.ClassNumber == criteria.ClassNumber )
;
if( criteria.AssigmentNumber != null )
{
query = query.Where( m => m.AssignmentNoteNumber == criteria.AssigmentNumber );
}
if( criteria.AccountNumber != null )
{
query = query.Where( m => m.AccountNumber == criteria.AccountNumber );
}
if( criteria.CourseId != null && criteria.CourseId.Value > 0 )
{
query = query.Where( m => m.BaseCourseId == criteria.CourseId );
}
if( criteria.ReferenceNumber != null )
{
query = query.Where( m => m.ReferenceNumber == criteria.ReferenceNumber );
}
if( criteria.FacultyCode != null )
{
query = query.Where( m => m.FacultyCode == criteria.FacultyCode );
}
if( criteria.ClassNumber != null )
{
query = query.Where( m => m.ClassNumber == criteria.ClassNumber );
}
List<AssignementMasterData> rows = await query.ToListAsync().ConfigureAwait(false);
List<String> categories = rows
.SelectMany( r => r.AssignmentNoteIdentifiedClasses )
.Select( String.IsNullOrEmpty(e.Category)? "(!)": e.Category) )
.ToList();
return categories;
}
The above can be simplified by adding a new extension-method (make sure you use Expression<Func<...>>
and not just Func<>
so that EF can still interpret the query:
public static class MyQueryableExtensions
{
public static IQueryable<T> WhereIfNotNull<T,TValue>( this IQueryable<T> query, TValue? value, Expression<Func<T,Boolean>> predicate )
where TValue : struct
{
if( value.HasValue && value.Value != default(TValue) )
{
return query.Where( predicate );
}
else
{
return query;
}
}
}
Used like so:
// `criteria` is now named `c` for brevity.
DateTime filterStart = c.FilterStartDate.Date;
DateTime filterEndExcl = c.FilterEndDate .Date.AddDays(1);
using (SUPEntities db = new SUPEntities())
{
IQueryable<AssignementMasterData> query = db.AssignementMasterDatas
.Include( m => m.AssignmentNoteIdentifiedClasses )
.Where( m => m.CreatedDateTime >= filterStart )
.Where( m => m.CreatedDateTime < filterEndExcl ) // Exclusive upper-bound.
.Where( m => m.Processed == c.Processed )
.Where( m => m.ClassNumber == c.ClassNumber )
.WhereIfNotNull( c.AssigmentNumber, m => m.AssignmentNoteNumber == c.AssigmentNumber )
.WhereIfNotNull( c.AccountNumber , m => m.AccountNumber == c.AccountNumber )
.WhereIfNotNull( c.CourseId , m => m.BaseCourseId == c.CourseId )
.WhereIfNotNull( c.ReferenceNumber, m => m.ReferenceNumberr == c.ReferenceNumber )
.WhereIfNotNull( c.FacultyCode , m => m.FacultyCoder == c.FacultyCode )
.WhereIfNotNull( c.ClassNumber , m => m.ClassNumber == c.ClassNumber )
;
List<AssignementMasterData> rows = await query.ToListAsync().ConfigureAwait(false);
List<String> categories = rows
.SelectMany( r => r.AssignmentNoteIdentifiedClasses )
.Select( String.IsNullOrEmpty(e.Category)? "(!)": e.Category) )
.ToList();
return categories;
}
CodePudding user response:
Firstly, about the error message:
You are trying to assign the wrong type to your masterDatas
variable.
You declare it as a IEnumerable<Respositories.AssignmentMasterData>
, but the ForEachAsync
at the last line will return a Task
, hence the error message.
See the ForEachAsync
signature:
public static System.Threading.Tasks.Task ForEachAsync (this System.Linq.IQueryable source, Action action);
Secondly. You want to return an IEnumerable<Respositories.AssignmentMasterData>
If you can content with a synchronous method, you could do it this way:
You will need to some point transform your IQueryable
into an IEnumerable
. A call to AsEnumerable()
does that. Then you need to replace some values. So you need to project your collection using a Select
.
using (SUPEntities db = new SUPEntities())
{
var masterDatas = db.AssignementMasterDatas
.Where(m => DbFunctions.TruncateTime(m.CreatedDateTime) >= DbFunctions.TruncateTime(criteria.FilterStartDate)
&& DbFunctions.TruncateTime(m.CreatedDateTime) <= DbFunctions.TruncateTime(criteria.FilterEndDate)
&& (m.AssignmentNoteNumber == criteria.AssigmentNumber || criteria.AssignmentNumber == null)
&& (m.BaseCourseId == criteria.courseId || criteria.CourseId == 0)
&& (m.AccountNumber == criteria.AccountNumber || criteria.AccountNumber == null)
&& (m.ReferenceNumber == criteria.ReferenceNumber || criteria.ReferenceNumber == null)
&& (m.FacultyCode == criteria.FAcultyCode || criteria.FacultyCode == null)
&& (m.Processed == criteria.Processed)
&& (m.ClassNumber == criteria.ClassNumber || criteria.ClassNumber == null))
.AsEnumerable()
.Select(a =>
{
a.AssignmentNoteIdentifiedClasses = a.AssignmentNoteIdentifiedClasses
.Select(e =>
{
e.Category = string.IsNullOrWhiteSpace(e.Category) ? "(!)" : e.Category;
return e;
})
.ToList(); // Depending on the type of AssignmentNoteIdentifiedClasses, ToList() might be replaced.
return a;
});
return masterDatas;
}