Question 181321 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – how to optimize the queries that have where condition on subitem

currently i`m using very bad query to get the data i whant

var map = await mapRepository.SingleAsync(x => x.Slug == slug);

            var groups = await repository.Queryable()
           .Include(x => x.Categories.Where(c => c.Locations.Any(l => l.MapId == map.Id))) // if not exist gets non relative Categories to map
           .ThenInclude(x => x.Locations.Where(l => l.MapId == map.Id)) // if not exist gets non relative locations to map
           .Where(x => x.Categories.Any(x => x.Locations.Any(x => x.MapId == map.Id)))// if not exist gets non relative groups to map
           .Select(x => mapper.Map<GroupViewModel>(x))
           .ToListAsync();

the db diagram is like this

enter image description here

the output data is something like this the groups and categories even the location count must be related to map

 {
        "title": "Collectibles",
        "order": 10,
        "color": "597698",
        "expandable": false,
        "categories": [
            {
                "title": "Bobblehead",
                "description": null,
                "info": null,
                "icon": "bobblehead",
                "template": null,
                "order": 1,
                "visible": true,
                "hasHeatmap": false,
                "featuresEnabled": false,
                "ignEnabled": true,
                "count": 5
            },
            {
                "title": "Holotape",
                "description": null,
                "info": null,
                "icon": "holotape",
                "template": null,
                "order": 1,
                "visible": true,
                "hasHeatmap": false,
                "featuresEnabled": false,
                "ignEnabled": true,
                "count": 7
            },
            {
                "title": "Perk Magazine",
                "description": null,
                "info": null,
                "icon": "perk_magazine",
                "template": null,
                "order": 1,
                "visible": true,
                "hasHeatmap": false,
                "featuresEnabled": false,
                "ignEnabled": true,
                "count": 4
            }
        ]
    },

the current code works exactly as in must be but i know this not proper way please someone help

Tags:

2

Answers


  1. 0

    to optimize the code and make it more efficient you can use the join method to perform a single query with the necessary joins

    Login or Signup to reply.
  2. 0

    Based on the database diagram you provided, it seems like you have a many-to-many relationship between Groups and Categories through the GroupCategories table, and a many-to-many relationship between Categories and Locations through the CategoryLocations table. You also have a one-to-many relationship between Maps and Locations.

    To get the data you want, you can try the following query:

    var groups = await repository.Queryable()
    .Where(x => x.GroupCategories.Any(gc => gc.Group.Map.Slug == slug)) // filter by map slug
    .Select(x => new
    {
        Group = mapper.Map<GroupViewModel>(x),
        Categories = x.GroupCategories
            .Where(gc => gc.Category.CategoryLocations.Any(cl => cl.Location.Map.Slug == slug)) // filter by map slug
            .Select(gc => new
            {
                Category = mapper.Map<CategoryViewModel>(gc.Category),
                Locations = gc.Category.CategoryLocations
                    .Where(cl => cl.Location.Map.Slug == slug) // filter by map slug
                    .Select(cl => mapper.Map<LocationViewModel>(cl.Location))
                    .ToList()
            })
            .ToList()
    })
    .ToListAsync();

    convert the anonymous type to the desired output format

    var result = groups.Select(g => new GroupViewModel
{
    Title = g.Group.Title,
    Order = g.Group.Order,
    Color = g.Group.Color,
    Expandable = g.Group.Expandable,
    Categories = g.Categories.Select(c => new CategoryViewModel
    {
        Title = c.Category.Title,
        Description = c.Category.Description,
        Info = c.Category.Info,
        Icon = c.Category.Icon,
        Template = c.Category.Template,
        Order = c.Category.Order,
        Visible = c.Category.Visible,
        HasHeatmap = c.Category.HasHeatmap,
        FeaturesEnabled = c.Category.FeaturesEnabled,
        IgnEnabled = c.Category.IgnEnabled,
        Count = c.Locations.Count
    }).ToList()
}).ToList();

    This query first filters the Groups by the slug parameter, then selects the GroupViewModel for each group and the related Categories that have at least one related Location with the same slug. For each Category, it selects the CategoryViewModel and the related Locations, and filters them by slug. Finally, it converts the anonymous type to the desired output format.

    Note that this query may need to be adjusted based on the exact relationships and requirements of your data model.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search