Access Navigation Property of type ICollection

Rajbir Singh

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....

Raein Hashemi

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Get ICollection property of generic type

From Dev

Unable to determine the relationship represented by navigation 'entitiy' of type 'ICollection<entity>'

From Dev

why do we need to create new instence of Icollection type property

From Dev

why do we need to create new instence of Icollection type property

From Dev

How to reference a ICollection property?

From Dev

swift access property of previous viewcontroller in navigation stack

From Dev

Casting ICollection of a concrete type to an ICollection of the concrete type interface

From Dev

Extract type ICollection<> from return type Task<ICollection<>>

From Dev

ICollection in interface with another interface as the type

From Dev

Get properties that are of type ICollection<BaseEntity>

From Dev

EF-Code First navigation property foreign key in complex type

From Dev

React-Navigation and TypeScript. Property 'getScreen' is missing in type

From Dev

Entity Framework Search On Entity Property and Entity ICollection

From Dev

Lazy loading does not load ICollection property

From Dev

Check if ICollection contains an element based on a property

From Dev

The declared type of navigation property Project.Models.Customer.SubCustomers is not compatible with the result of the specified navigation

From Dev

The declared type of navigation property Project.Models.Customer.SubCustomers is not compatible with the result of the specified navigation

From Dev

How to check if type implements ICollection<T>

From Dev

How to check if a state object is of type ICollection

From Dev

How to map ICollection<Type> to extending class

From Dev

How to check if a state object is of type ICollection

From Dev

Convert ICollection<Object> to another type ICollection<Person> using variable type of Type

From Java

Cannot access property on Swift type from Objective-C

From Dev

MS Access Control Property.Type not making sense

From Dev

Access of possibly undefined property alpha through a reference with static type Class

From Dev

OData - $expand on navigation property of non navigation property

From Dev

react native component has no access to props - TypeError: Cannot read property ‘navigation’ of undefined

From Dev

BreezeJs Navigation Property Count

From Dev

use ToListAsync() with navigation property

Related Related

  1. 1

    Get ICollection property of generic type

  2. 2

    Unable to determine the relationship represented by navigation 'entitiy' of type 'ICollection<entity>'

  3. 3

    why do we need to create new instence of Icollection type property

  4. 4

    why do we need to create new instence of Icollection type property

  5. 5

    How to reference a ICollection property?

  6. 6

    swift access property of previous viewcontroller in navigation stack

  7. 7

    Casting ICollection of a concrete type to an ICollection of the concrete type interface

  8. 8

    Extract type ICollection<> from return type Task<ICollection<>>

  9. 9

    ICollection in interface with another interface as the type

  10. 10

    Get properties that are of type ICollection<BaseEntity>

  11. 11

    EF-Code First navigation property foreign key in complex type

  12. 12

    React-Navigation and TypeScript. Property 'getScreen' is missing in type

  13. 13

    Entity Framework Search On Entity Property and Entity ICollection

  14. 14

    Lazy loading does not load ICollection property

  15. 15

    Check if ICollection contains an element based on a property

  16. 16

    The declared type of navigation property Project.Models.Customer.SubCustomers is not compatible with the result of the specified navigation

  17. 17

    The declared type of navigation property Project.Models.Customer.SubCustomers is not compatible with the result of the specified navigation

  18. 18

    How to check if type implements ICollection<T>

  19. 19

    How to check if a state object is of type ICollection

  20. 20

    How to map ICollection<Type> to extending class

  21. 21

    How to check if a state object is of type ICollection

  22. 22

    Convert ICollection<Object> to another type ICollection<Person> using variable type of Type

  23. 23

    Cannot access property on Swift type from Objective-C

  24. 24

    MS Access Control Property.Type not making sense

  25. 25

    Access of possibly undefined property alpha through a reference with static type Class

  26. 26

    OData - $expand on navigation property of non navigation property

  27. 27

    react native component has no access to props - TypeError: Cannot read property ‘navigation’ of undefined

  28. 28

    BreezeJs Navigation Property Count

  29. 29

    use ToListAsync() with navigation property

HotTag

Archive