Returning multiple joined/navigation objects

Question

I recently inherited an ASP.NET Entity Framework API project.

I managed to do basic changes that were needed, but I'm getting a little lost with advanced SQL requests (I'm more a Symfony dev, I don't know Entity Framework).

I need to add a GET method to my API that will take a service (prestation) and will return a list of all the partners (Partenaires) who has a service (Prestation) with the same name as the GET parameter.

Here's the actual prototype of the method:

// GET: api/Partenaires_prestations
[Authorize]
public List<PartenaireMapItem> GetPartenairesWithPrestations(string prestation = "")
{    
    if (string.IsNullOrWhiteSpace(prestation))
    {
        prestation = "";
    }

    return db.Partenaires
                .Join() // Here I don't know what to do
}

I have 3 tables that I have to use:

Prestation, Partenaires, PartenairesPrestation

Here are the 3 tables classes:

namespace Uphair.EfModel
{
    using System;
    using System.Collections.Generic;

    public partial class Partenaire
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Partenaire()
        {
            this.PartenairePrestations = new HashSet<PartenairePrestation>(); // I took out the useless parts
        }

        public int IdPartenaire { get; set; }
        [...] // I took out properties that are not needed

        public virtual ICollection<PartenairePrestation> PartenairePrestations { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
}

namespace Uphair.EfModel
{
    using System;
    using System.Collections.Generic;

    public partial class PartenairePrestation
    {
        public int IdPartenaire { get; set; }
        public int IdPrestation { get; set; }
        public double Prix { get; set; }
        public int Duree { get; set; }
        public Nullable<System.DateTime> DateAjout { get; set; }

        public virtual Partenaire Partenaire { get; set; }
        public virtual Prestation Prestation { get; set; }
    }
}

namespace Uphair.EfModel
{
    using System;
    using System.Collections.Generic;

    public partial class Prestation
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Prestation()
        {
            this.PartenairePrestations = new HashSet<PartenairePrestation>();
            this.PrixDureeOptions = new HashSet<PrixDureeOption>();
            this.LigneReservations = new HashSet<LigneReservation>();
        }

        public int IdPrestation { get; set; }
        public string NomPrestation { get; set; }
        public int Categorie { get; set; }
        public Nullable<int> CoifEsthe { get; set; }
        public Nullable<int> IdPrestationCategorie { get; set; }

        public virtual ICollection<PartenairePrestation> PartenairePrestations { get; set; }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
}

The last one makes the link between Partenaires and Prestation via IdPartenaire and IdPrestation columns.

Here's a screenshot of my model structure:

https://imageshack.com/a/img922/9111/4twiC8.png (imgur is not responding sorry)

Here's the PartenaireMapItem model:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Uphair.EfModel;

namespace Uphair.Api.Models.Partenaire
{
    public class PartenaireMapItem
    {
        public int IdPartenaire { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string NomComplet { get; set; }
        public double? Lat { get; set; }
        public double? Lng { get; set; }
        public PartenaireType Type { get; set; }

        public int DureeMin { get; set; }

        public string ImageUrl { get; set; }

        public int SeDeplace { get; set; }

        public bool ADomicile { get; set; }

        public double NoteGlobale { get; set; }


        public List<String> Prestations { get; set; } 
    }
}

I need to use join to get a list of partners (Partenaires) that have the service (Prestation) with the service name provided in the GET parameter, but I don't know how to do it.

Any help would be welcome, thanks to anyone who will take the time to read/answer me.

A properly designed model with [navigation properties](https://msdn.microsoft.com/en-us/library/jj713564(v=vs.113).aspx) does not need joins. That is one of the big benefits of Entity Framework. — Steve Greene, Aug 07 '18 at 16:19
@SteveGreene I have navigation properties in my edmx, I'll go read your link. Thanks for your help :) — Pierrick Martellière, Aug 07 '18 at 16:23
@SteveGreene your link is only giving example with Navigation Properties between two tables. As you can see in my model diagram screenshot (https://imageshack.com/a/img922/9111/4twiC8.png), I have a third table to link ```Partenaires``` with ```Prestations``` named ```PartenairesPrestation```. Any example would be very useful. — Pierrick Martellière, Aug 07 '18 at 16:27
I will edit my post with the three entity tables class from my model. — Pierrick Martellière, Aug 07 '18 at 16:29
https://stackoverflow.com/questions/35655428/entity-framework-include-multiple-level-properties — Steve Greene, Aug 07 '18 at 16:34
Something like `context.PartenairePrestations.Include(p => p.Partenaire).Include(p => p.Prestation).ToList();` — Steve Greene, Aug 07 '18 at 16:39
Nice, thanks, so what if I want to Select only the Partenaire who has ```prestationName``` (variable) in it please ? — Pierrick Martellière, Aug 07 '18 at 16:43
Sorry to ask maybe dumb questions but this is a side project that I didn't do on my own and I'm not familiar with advanced entity framework techniques. — Pierrick Martellière, Aug 07 '18 at 16:48
@PierrickMartellière You can do whatever you like - add `Where`, `Select` (in that case you won't need `Include`). For more info about using navigation properties in LINQ to Entities queries, see [Don’t use Linq’s Join. Navigate!](https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/). You basically code the query as if its against objects with collections. For instance, `db.Partenaires.Where(p => p.PartenairePrestations.Any(pp => pp.Prestation.NomPrestation == prestation)).Select(p => new PartenaireMapItem { IdPartenaire = p.IdPartenaire, ...})` — Ivan Stoev, Aug 07 '18 at 17:26
Thank you very much @IvanStoev ! That was exactly what I needed. — Pierrick Martellière, Aug 07 '18 at 17:28
I tested the request, but I get the error : > LINQ to Entities does not recognize the method 'Char get_Chars(Int32)' method, and this method cannot be translated into a store expression. I used your request. — Pierrick Martellière, Aug 09 '18 at 14:50

Returning multiple joined/navigation objects

0 Answers0