I have two Entity Classes
namespace REApplications.BLL
{
using System;
using System.Collections.Generic;
public partial class Hr
{
public Hr()
{
this.HrCompensation = new HashSet<HrCompensation>();
this.HrSplitPlan = new HashSet<HrSplitPlan>();
}
public const string Schema_Id = "Id";
public int Id { get; set; }
public const string Schema_ContactId = "ContactId";
public int ContactId { get; set; }
public const string Schema_BaseOfficeLocation = "BaseOfficeLocation";
public string BaseOfficeLocation { get; set; }
public const string Schema_Type = "Type";
public string Type { get; set; }
public const string Schema_EmploymentStatus = "EmploymentStatus";
public string EmploymentStatus { get; set; }
public const string Schema_EmploymentStartDate = "EmploymentStartDate";
public Nullable<System.DateTime> EmploymentStartDate { get; set; }
public const string Schema_EmploymentEndDate = "EmploymentEndDate";
public Nullable<System.DateTime> EmploymentEndDate { get; set; }
public const string Schema_CreatedByContactId = "CreatedByContactId";
public Nullable<int> CreatedByContactId { get; set; }
public const string Schema_CreatedDateTime = "CreatedDateTime";
public Nullable<System.DateTime> CreatedDateTime { get; set; }
public const string Schema_RevisedByContactId = "RevisedByContactId";
public Nullable<int> RevisedByContactId { get; set; }
public const string Schema_RevisedDateTime = "RevisedDateTime";
public Nullable<System.DateTime> RevisedDateTime { get; set; }
public const string Schema_VerifiedByContactId = "VerifiedByContactId";
public Nullable<int> VerifiedByContactId { get; set; }
public const string Schema_VerifiedDate = "VerifiedDate";
public Nullable<System.DateTime> VerifiedDate { get; set; }
public const string Schema_PayLevel = "PayLevel";
public string PayLevel { get; set; }
public const string Schema_CompensationComments = "CompensationComments";
public string CompensationComments { get; set; }
public const string Schema_DisplayAs = "DisplayAs";
public string DisplayAs { get; set; }
public const string Schema_JobFunction = "JobFunction";
public string JobFunction { get; set; }
public const string Schema_ImportSource = "ImportSource";
public string ImportSource { get; set; }
public const string Schema_EmployeeCategory = "EmployeeCategory";
public string EmployeeCategory { get; set; }
public const string Schema_IsPrincipal = "IsPrincipal";
public Nullable<bool> IsPrincipal { get; set; }
public const string Schema_EarnsCommission = "EarnsCommission";
public Nullable<bool> EarnsCommission { get; set; }
public const string Schema_ReportsToContactId = "ReportsToContactId";
public Nullable<int> ReportsToContactId { get; set; }
public const string Schema_PersonnelComments = "PersonnelComments";
public string PersonnelComments { get; set; }
public const string Schema_ImportSourceId = "ImportSourceId";
public Nullable<int> ImportSourceId { get; set; }
public const string Schema_ImportTaskId = "ImportTaskId";
public Nullable<int> ImportTaskId { get; set; }
public const string Schema_EducationComments = "EducationComments";
public string EducationComments { get; set; }
public const string Schema_ExperienceComments = "ExperienceComments";
public string ExperienceComments { get; set; }
public const string Schema_QualificationComments = "QualificationComments";
public string QualificationComments { get; set; }
public const string Schema_IsActive = "IsActive";
public bool IsActive { get; set; }
public virtual ICollection<HrCompensation> HrCompensation { get; set; }
public virtual Contact Contact { get; set; }
public virtual Contact VerifiedByContact { get; set; }
public virtual Contact CreatedByContact { get; set; }
public virtual Contact RevisedByContact { get; set; }
public virtual Contact ReportsToContact { get; set; }
public virtual ICollection<HrSplitPlan> HrSplitPlan { get; set; }
}
}
and
namespace REApplications.BLL
{
using System;
using System.Collections.Generic;
public partial class HrCompensation
{
public const string Schema_Id = "Id";
public int Id { get; set; }
public const string Schema_HrId = "HrId";
public Nullable<int> HrId { get; set; }
public const string Schema_Type = "Type";
public string Type { get; set; }
public const string Schema_EmploymentStatus = "EmploymentStatus";
public string EmploymentStatus { get; set; }
public const string Schema_TransactionDate = "TransactionDate";
public Nullable<System.DateTime> TransactionDate { get; set; }
public const string Schema_Title = "Title";
public string Title { get; set; }
public const string Schema_Occupation = "Occupation";
public string Occupation { get; set; }
public const string Schema_Specialty = "Specialty";
public string Specialty { get; set; }
public const string Schema_EmployeeClassification = "EmployeeClassification";
public string EmployeeClassification { get; set; }
public const string Schema_PayRate = "PayRate";
public Nullable<decimal> PayRate { get; set; }
public const string Schema_PayFrequency = "PayFrequency";
public string PayFrequency { get; set; }
public const string Schema_Comments = "Comments";
public string Comments { get; set; }
public const string Schema_SplitPackage = "SplitPackage";
public string SplitPackage { get; set; }
public const string Schema_CommissionScheduleComments = "CommissionScheduleComments";
public string CommissionScheduleComments { get; set; }
public const string Schema_LeaveOfAbsenceStartDate = "LeaveOfAbsenceStartDate";
public Nullable<System.DateTime> LeaveOfAbsenceStartDate { get; set; }
public const string Schema_LeaveOfAbsenceEstimatedReturnDate = "LeaveOfAbsenceEstimatedReturnDate";
public Nullable<System.DateTime> LeaveOfAbsenceEstimatedReturnDate { get; set; }
public const string Schema_LeaveOfAbsenceActualReturnDate = "LeaveOfAbsenceActualReturnDate";
public Nullable<System.DateTime> LeaveOfAbsenceActualReturnDate { get; set; }
public const string Schema_TerminationDate = "TerminationDate";
public Nullable<System.DateTime> TerminationDate { get; set; }
public const string Schema_VacationRemaining = "VacationRemaining";
public Nullable<decimal> VacationRemaining { get; set; }
public const string Schema_TerminationReason = "TerminationReason";
public string TerminationReason { get; set; }
public const string Schema_TerminationComments = "TerminationComments";
public string TerminationComments { get; set; }
public const string Schema_CreatedByContactId = "CreatedByContactId";
public Nullable<int> CreatedByContactId { get; set; }
public const string Schema_CreatedDateTime = "CreatedDateTime";
public Nullable<System.DateTime> CreatedDateTime { get; set; }
public const string Schema_RevisedByContactId = "RevisedByContactId";
public Nullable<int> RevisedByContactId { get; set; }
public const string Schema_RevisedDateTime = "RevisedDateTime";
public Nullable<System.DateTime> RevisedDateTime { get; set; }
public const string Schema_VerifiedByContactId = "VerifiedByContactId";
public Nullable<int> VerifiedByContactId { get; set; }
public const string Schema_VerifiedDate = "VerifiedDate";
public Nullable<System.DateTime> VerifiedDate { get; set; }
public const string Schema_ImportSource = "ImportSource";
public string ImportSource { get; set; }
public const string Schema_FiscalStartMonth = "FiscalStartMonth";
public Nullable<byte> FiscalStartMonth { get; set; }
public const string Schema_ImportSourceId = "ImportSourceId";
public Nullable<int> ImportSourceId { get; set; }
public const string Schema_ImportTaskId = "ImportTaskId";
public Nullable<int> ImportTaskId { get; set; }
public const string Schema_BonusPercent = "BonusPercent";
public Nullable<decimal> BonusPercent { get; set; }
public const string Schema_BonusAmount = "BonusAmount";
public Nullable<decimal> BonusAmount { get; set; }
public const string Schema_BonusFrequency = "BonusFrequency";
public string BonusFrequency { get; set; }
public virtual Hr Hr { get; set; }
public virtual Contact VerifiedByContact { get; set; }
}
}
Then I have a Repository class like
namespace REApplications.BLL.Repositories
{
using System.Data.Entity;
using System.Linq;
using System;
using REApplications.Common.User;
using REApplications.BLL.ViewModels;
public class HrRepository : BaseRepository<Hr>, IHrRepository
{
#region Setup
protected override IDbSet<Hr> DbSet { get { return DbContext.Hr; } }
/// <summary>
/// Use this contructor when a dbContext doesn't exist.
/// This will create a dbContext object and use that to persist data.
/// This constructor is also important for the repository to show up as a data source when using for SSRS reports.
/// </summary>
public HrRepository() : base() { }
/// <summary>
/// Use this constructor to pass an existing dbContext.
/// This is useful if multiple operations to the database need to be batched
/// together in a single operation.
/// </summary>
/// <param name="dbContext"></param>
public HrRepository(IDataModelContext dbContext, Identity identity) : base(dbContext, identity) { }
Public IQueryable<Hr> SearchAll
(
Parameters.ISearchParameters<Hr> searchParams = null
, string sortBy = ProjectConstants.DefaultSortKey
, System.Data.SqlClient.SortOrder sortDirection = System.Data.SqlClient.SortOrder.Ascending
)
{
var query = base.SearchAll()
.Where(hr => hr.IsActive)
.AsQueryable();
// Apply search filtering
if (searchParams != null)
{
query = searchParams.ToFilteredExpression(query);
}
// Apply sorting
query = query.DynamicOrderBy(sortBy, sortDirection);
return query;
}
}
}
in which I am doing query = searchParams.ToFilteredExpression(query);
and applying the search filter like this
namespace REApplications.BLL.Parameters
{
using System.Linq;
public class HrSearchParameters : ISearchParameters<Hr>
{
public int? ContactId;
public HrCompensationSearchParameters HrCompensation = null;
/// <summary>
/// Bulid the query based on search params
/// </summary>
/// <param name="query">Apply filters to the this parameter</param>
/// <returns></returns>
public IQueryable<BLL.Hr> ToFilteredExpression(IQueryable<BLL.Hr> query)
{
if (ContactId.HasValue)
{
query = query.Where(x => x.ContactId == ContactId);
}
if (HrCompensation != null)
{
query = HrCompensation.ToFilteredExpression(query);
}
return query;
}
}
}
and
namespace REApplications.BLL.Parameters
{
using System.Linq;
public class HrCompensationSearchParameters : ISearchParameters<HrCompensation>
{
public DateRangeSearchParameter TransactionDate = null;
/// <summary>
/// Checks each search parameter and returns the built query expression.
/// </summary>
/// <param name="query">The query to which the filters will be applied.</param>
/// <returns></returns>
///
public IQueryable<BLL.Hr> ToFilteredExpression(IQueryable<BLL.Hr> query)
{
if (TransactionDate != null)
{
if (TransactionDate.Min.HasValue)
{
query = query.Where(x => (x.HrCompensation.Any(hr => hr.TransactionDate >= TransactionDate.Min)));
}
if (TransactionDate.Max.HasValue)
{
query = query.Where(x => (x.HrCompensation.Any(hr => hr.TransactionDate <= TransactionDate.Max)));
}
}
return query;
}
public IQueryable<BLL.HrCompensation> ToFilteredExpression(IQueryable<BLL.HrCompensation> query)
{
if (TransactionDate != null)
{
if (TransactionDate.Min.HasValue)
{
query = query.Where(x => (x.TransactionDate >= TransactionDate.Min));
}
if (TransactionDate.Max.HasValue)
{
query = query.Where(x => (!x.TransactionDate.HasValue) || (x.TransactionDate <= TransactionDate.Max));
}
}
return query;
}
}
}
in HrCompensationSearchParameters
file I am doing
query = query.Where(x => (x.HrCompensation
.Any(hr => hr.TransactionDate >= ransactionDate.Min)));
by which when sql query returns it will add an EXISTS clause into the query and the filter in EXISTS that will return all the records from HrCompensation
if it get at least one record in EXISTS.
I want to filter the whole query not want to add Exist . if I do
query = query.Where(x => x.HrCompensation
.Where(y => y.TransactionDate >= TransactionDate.Min));
it will returns an error that
IQueryable can not be converted to bool
can anybody suggest anything....
List.Where
doesnt return bool. List.Any
does. In a Where
clause it checks if a condition is made for each record and includes it in the result LIST! But in Any
it returns true if it finds only one record with that condition. So yes, it should give you that error. You can use this:
query = query.Where(
x => x.HrCompensation.Where(
y => y.TransactionDate >= TransactionDate.Min).FirstOrDefault() != null);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments