CODEWITHSUNDEEP
asp.net-mvcentity-framework-6many-to-many
clarence_odbody
clarence_odbody

Reputation: 185

Entity Framework - query many to many

Using MVC EF Code First.
I have 3 classes. One creates a many-many relationship between the other two. I believe I have this set up correctly, it created the tables correctly in the database. My problem is in trying to query them. I am trying to return a collection of pictures where !Picture.Del, Picture.OK2Publish is either true/false/all and Pictures_Tag.ImageTag_ID==tagid (per user input). Code below

public class Picture
{
    public int ID {get;set;}
    public string Path {get;set;}
    public string Caption {get;set;}
    public bool Ok2Publish {get;set;}
    public bool Del {get;set;}
}
public class ImageTag
{
    public int ID {get;set;}
    public string Tag {get;set;}
}
public class Pictures_Tag
{
    [Key]
    [Column(Order = 0)]
    [ForeignKey("Picture")]
    public Int16 Picture_ID { get; set; }
    [Key]
    [Column(Order = 1)]
    [ForeignKey("ImageTag")]
    public Int16 ImageTag_ID { get; set; }

    public virtual Picture Picture { get; set; }
    public virtual ImageTag ImageTag { get; set; }

    public byte PictureOrder { get; set; }
    public bool ShowInGallery { get; set; }
}

var px = db.Pictures
        .Include(b => b.Pictures_Tags).Include((Picture q) => .Pictures_Tags
        .Select((Pictures_Tag r) => r.ImageTag_ID == tagid ))
         .Where(c => !c.Del).OrderBy(z => z.PictureName);

if (searchPublish != "A")//show all 
{
    px = (searchPublish == "Y") ? px.Where(n => n.OK2Publish == true) : 
                                  px.Where(n => n.OK2Publish == false);
}

Upvotes: 0

Views: 35

Answers (1)

Luke Villanueva
Luke Villanueva

Reputation: 2070

Change your query into this

var px = db.Pictures.Include(b => b.Pictures_Tags)
.Where(c => !c.Del && c.Pictures_Tags.Count(d => d.ImageTag_ID == tagid) > 0)
.OrderBy(z => z.PictureName);

It will return the pictures that are not deleted and if their tags has the tagId supplied. I think you don't need to transform the data and perform a select.

Upvotes: 1

Related Questions