Not able to apply filter to the property of a collection object in odata

0
votes

I am using the following code in C# Web API OData and trying to do a filter on the LanguageId, but I am getting the below given error when applying the filter

Models

namespace ODataSample
{
    public class Project
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime UpdatedOn { get; set; }
        public long CreatedBy { get; set; }
        public long UpdatedBy { get; set; }
        public decimal Cost { get; set; }
        public long StatusId { get; set; }
        [ForeignKey("StatusId")]
        public virtual ProjectStatus Status { get; set; }
    }

    public class ProjectStatus
    {
        public ProjectStatus()
        {
            ProjectStatusTexts = new HashSet<ProjectStatusText>();
        }
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long StatusId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public bool IsActive { get; set; }
        public virtual ICollection<ProjectStatusText> ProjectStatusTexts { get; set; }
        public virtual ICollection<Project> Projects { get; set; }
    }

    public class ProjectStatusText
    {
        public ProjectStatusText()
        {
            ProjectStatus = new HashSet<ProjectStatus>();
        }

        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        public long StatusId { get; set; }
        [ForeignKey("StatusId")]
        public IEnumerable<ProjectStatus> ProjectStatus { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public long LanguageId { get; set; }
        [ForeignKey("LanguageId")]
        public virtual Language Language { get; set; }
        public bool IsActive { get; set; }
    }

    public class Language
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long LanguageId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public bool IsActive { get; set; }
    }

    public class User
    {
        public User()
        {
            this.Projects = new HashSet<Project>();
        }

        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long UserId { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public bool IsActive { get; set; }
        public virtual ICollection<Project> Projects { get; set; }
    }
}

The services for the above model

namespace ODataSample.Services
{
    public class ProjectsService
    {
        public IQueryable<Project> GetProjects()
        {
            var ctx = new ProjectsContext();

            return ctx.Projects;
        }
    }
}

The DbContext with the Seed data and DbInitializer

namespace ODataSample.Contexts
{
    public class ProjectsContext : DbContext
    {
        public ProjectsContext() : base("ProjectsConnectionString") { Database.SetInitializer(new ProjectDBInitializer()); }
        public ProjectsContext(string nameOrConnectionString) : base(nameOrConnectionString)
        {
            Database.SetInitializer(new ProjectDBInitializer());
        }

        public DbSet<Project> Projects { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<ProjectStatus> ProjectStatuses { get; set; }
        public DbSet<ProjectStatusText> ProjectStatusTexts { get; set; }
        public DbSet<Language> Languages { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }

    public class ProjectDBInitializer : DropCreateDatabaseAlways<ProjectsContext>
    {
        public ProjectDBInitializer()
        {
        }

        protected override void Seed(ProjectsContext context)
        {
            context.Languages.AddRange(new[]
            {
                new Language { Description = "Spanish", IsActive = true, LanguageId = 1, Name = "es" },
                new Language { Description = "English", IsActive = true, LanguageId = 2, Name = "en" }
            });

            context.ProjectStatuses.AddRange(new[]
            {
                new ProjectStatus { StatusId=1, Name="Active", Description="Active", IsActive= true },
                new ProjectStatus { StatusId=2, Name="Closed", Description="Closed", IsActive= true },
                new ProjectStatus { StatusId=3, Name="InProgress", Description="In Progress", IsActive= true }
            });

            context.ProjectStatusTexts.AddRange(new[]
            {
                new ProjectStatusText {Id = 1, LanguageId=1, StatusId = 1,Name = Path.GetRandomFileName(),Description = Path.GetRandomFileName(),IsActive=true },
                new ProjectStatusText {Id = 2, LanguageId=2, StatusId = 1,Name = "Active",Description = "Active",IsActive=true },
                new ProjectStatusText {Id = 3, LanguageId=1, StatusId = 2,Name = Path.GetRandomFileName(),Description = Path.GetRandomFileName(),IsActive=true },
                new ProjectStatusText {Id = 4, LanguageId=2, StatusId = 3,Name = "InProgress",Description = "InProgress",IsActive=true }
            });

            context.Projects.AddRange(new[]
            {
                new Project{ Cost = default(decimal),  Id=1,Name="project 1",StatusId = 1, Description="project 1", CreatedOn = DateTime.Now, UpdatedOn = DateTime.Now,  CreatedBy = 1, UpdatedBy =1},
                new Project{ Cost = default(decimal), Id=2,Name="project 2",StatusId = 1, Description="project 2", CreatedOn = DateTime.Now, UpdatedOn = DateTime.Now,  CreatedBy = 1, UpdatedBy =1},
                new Project{ Cost = default(decimal), Id=3,Name="project 3",StatusId = 1, Description="project 3", CreatedOn = DateTime.Now, UpdatedOn = DateTime.Now, CreatedBy = 1, UpdatedBy =1 }
            });

            base.Seed(context);
        }
    }
}

The following is the WebApiConfig registrations made

var modelBuilder = new ODataConventionModelBuilder();
modelBuilder.EntitySet<Project>("Projects");
modelBuilder.EntitySet<ProjectStatus>("ProjectStatusses");
modelBuilder.EntitySet<ProjectStatusText>("ProjectStatusTexts");
modelBuilder.EntitySet<Language>("Languages");
modelBuilder.EntitySet<User>("Users");
var edmModel = modelBuilder.GetEdmModel();
config.Routes.MapODataRoute("odata", "odata", edmModel);

The Problem: I am having the Project entity for which the statuses are available like new, active, inprogress etc. When I try to do a localization of these values, i find that the approach of having a text class / table will be easy. Hence the creation of the ProjectStatusTexts class / table.

When I try to get the projects with the translated values, I am using the following OData Query from the Browser,but it does not filter the records because the where condition generated by the EF for the any clause in the URI returns all the records from the ProjectStatusTexts table.

I have also tried the following approach given in StackOverflow, but could not be able to fix. Is there something wrong with this model that C# does not understand or is there something wrong with OData Query URI, please help.

Query URI: http://localhost:64046/odata/Projects?$expand=Status/ProjectStatusTexts&$filter=Status/ProjectStatusTexts/any(l:l/Language/LanguageId%20eq%202)

var projects = (EdmEntitySet)edmModel.EntityContainers().Single().FindEntitySet("Projects");
var projectStatusTexts = (EdmEntitySet)edmModel.EntityContainers().Single().FindEntitySet("ProjectStatusTexts");
var projectType = (EdmEntityType)edmModel.FindDeclaredType("ODataSample.Project");
var projectStatusTextsType = (EdmEntityType)edmModel.FindDeclaredType("ODataSample.ProjectStatusText");

var partsProperty = new EdmNavigationPropertyInfo();
partsProperty.TargetMultiplicity = EdmMultiplicity.Many;
partsProperty.Target = projectStatusTextsType;
partsProperty.ContainsTarget = false;
partsProperty.OnDelete = EdmOnDeleteAction.Cascade;
partsProperty.Name = "ProjectStatusTexts";

//projects.AddNavigationTarget(projectType.AddUnidirectionalNavigation(partsProperty), projectStatusTexts);

var navigationProperty = projectType.AddUnidirectionalNavigation(partsProperty);
projects.AddNavigationTarget(navigationProperty, projectStatusTexts);

var linkBuilder = edmModel.GetEntitySetLinkBuilder(projects);
linkBuilder.AddNavigationPropertyLinkBuilder(navigationProperty,
    new NavigationLinkBuilder((context, property) =>
        context.GenerateNavigationPropertyLink(property, false), true));

config.Routes.MapODataRoute("odata", "odata", edmModel);
2
c#entity-frameworkodata
I'm a little unclear about your data model, is it possible to provide some more detail about what these things represent and what you are trying to filter? - TomDoesCode
I am trying to filter the projects statusses by a given language. The project has a statusid which is taken from ProjectStatus and this is localized and the localized data is available from ProjectStatusTexts table for each language.When I get a project, I want to show the StatusText by the language of the user. ex: Spanish - Saran

2 Answers

0
votes

I think that what you are trying to achieve is to get all of the projects and then to expand to the ProjectStatusTexts that have the correct language. If that is the case then the filter is part of the expand not part of the query for the Projects themselves so it should be added in brackets after the expand like this:

http://localhost:64046/odata/Projects?$expand=Status/ProjectStatusTexts($filter=Language/LanguageId%20eq%202)

0
votes

Building on the answer from @TomDoesCode

In OP's schema, Project has a Status, and Status has many ProjectStatusText In the resultset, we only want to include ProjectStatusText records that match a given language id.

To achieve this, we need to expand twice, in the expansion to ProjectStatusTexts we also need to apply a filter.

http://localhost:64046/odata/Projects?$expand=Status($expand=ProjectStatusTexts($filter=Language/LanguageId eq 2))

This query will return all projects, but will only include the ProjectStatusText if it's language Id matches 'English' (2)

Note, to access multiple levels of navigation properties we must use nested $expand operators

OPs attempt to filter can be translated roughly to:

  • filter by Projects that have any status that has an english text

If you also wanted to only return projects with an english status, then you could combine the two approaches into the following:

http://localhost:64046/odata/Projects?$expand=Status($expand=ProjectStatusTexts($filter=Language/LanguageId eq 2))&$filter=Status/ProjectStatusTexts/any(l:l/Language/LanguageId eq 2)

However this type of filter is usually redundant, expecially if all project status records will have an entry for each supported language.