获取多对多关系中所有实体的数据 [英] Get data of all entities in a many-to-many relationship

问题描述

DB图片我的数据库有3个表Customers，Films和CustomersFilms是现在，在电影"和客户"之间架起了一座桥梁，我想用他们租用的电影来显示一张顾客的桌子. 我编写了一个函数，用于处理特殊ID(在代码8和9中).我的问题是如何向所有实体显示数据?

picture of DB My database has 3 tables Customers, Films and CustomersFilms is the brige table between Films and Customers now, I want to diaplay a table of cutomers with the movies they rented. I wrote a function that does the job for spesific id ( in the code 8 and 9 ). My Question is how to show the data to all entitys ?

   public ActionResult GetData()
    {
        MyDatabaseEntities2 db = new MyDatabaseEntities2();
        var q = (from c in db.Customers
                 from Films in db.Films
                     where Films.FilmId == 8
                     where c.CustomerId == 9
                 select new
                 {
                     c.CustomerName,
                     c.Phone,
                     c.FilmId,
                     c.CustomerId,
                      FilmName = Films.FilmName

                 }).ToList();
        return Json(new { data = q }, JsonRequestBehavior.AllowGet);
    }

添加我的课程

namespace MoviePro.Models
{
using System;
using System.Collections.Generic;

public partial class Films
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
 "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Films()
    {
        this.Customers = new HashSet<Customers>();
    }

    public int FilmId { get; set; }
    public string FilmName { get; set; }
    public string Length { get; set; }
    public string Genre { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
    "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Customers> Customers { get; set; }
    }
    }
    public partial class Customers
    {
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
    "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Customers()
    {
        this.Films = new HashSet<Films>();
    }

    public int CustomerId { get; set; }
    public string CustomerName { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public Nullable<int> FilmId { get; set; }

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

非常感谢您

****有效的新代码****

****New CODE THAT WORKS****

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MoviePro.Models;
using System.Data.Entity;

namespace MoviePro.Controllers
{
public class CustomersController : Controller
{
    // GET: Customers
    public ActionResult Index()
    {
        return View();
    }
    public ActionResult GetData()
    {
        MyDatabaseEntities2 db = new MyDatabaseEntities2();
        var q = (from c in db.Customers
                 from Films in c.Films
                 where Films.FilmId == Films.FilmId
                 where c.CustomerId == c.CustomerId
                 select new
                 {
                     c.CustomerName,
                     c.Phone,
                     c.FilmId,
                     c.CustomerId,
                     FilmName = Films.FilmName

                 }).ToList();
        return Json(new { data = q }, JsonRequestBehavior.AllowGet);
    }

    public ActionResult loaddata()
    {
        MyDatabaseEntities2 dc = new MyDatabaseEntities2();

        var customers = dc.Customers.Select(c => new
        {
            c.CustomerName,
            c.Phone,
            c.FilmId,
            c.CustomerId,


        });

        return Json(new { data = customers }, JsonRequestBehavior.AllowGet);

    }

    public ActionResult AddOrEdit(int id = 0)
    {
        if (id == 0)
            return View(new Customers());

        else
        {
            using (MyDatabaseEntities2 db = new MyDatabaseEntities2())
            {
                return View(db.Customers.Where(x => x.CustomerId== 
   id).FirstOrDefault<Customers>());

            }
        }
    }

    [HttpPost]
    public ActionResult AddOrEdit(Customers customer)
    {
        using (MyDatabaseEntities2 db = new MyDatabaseEntities2())
        {
            if (customer.CustomerId == 0)
            { 
                db.Customers.Add(customer);
                db.SaveChanges();
                return Json(new { success = true, message = "Saved 
      Successfully" }, JsonRequestBehavior.AllowGet);
            }
            else
            {
                db.Entry(customer).State = EntityState.Modified;
                db.SaveChanges();
                return Json(new { success = true, message = "Updated 
        Successfully" }, JsonRequestBehavior.AllowGet);
            }
        }


    }

    [HttpPost]
    public ActionResult Delete(int id)
    {
        using (MyDatabaseEntities2 db = new MyDatabaseEntities2())
        {
            Customers emp = db.Customers.Where(x => x.CustomerId == 
      id).FirstOrDefault<Customers>();

            db.Customers.Remove(emp);
            db.SaveChanges();
            return Json(new { success = true, message = "Deleted 
       Successfully" }, JsonRequestBehavior.AllowGet);
        }
        }
       }
       }

推荐答案

用于查询多对多关系的基本LINQ查询形状为:

The basic LINQ query shape for querying many-to-many relationships is:

from c in db.Customers
from f in c.Films // NOT db.Films
select new
{
    Customer = c.Customername,
    Film = f.FilmName,
    ...
}

这是与SelectMany等效的查询语法.结果是一个平面数据列表，因此通常称为平面化".

This is the query syntax equivalent of SelectMany. The result is a flat list of data, hence this is commonly referred to as "flattening".

通常，显示嵌套列表更有用:

Often it's more useful to show a nested list:

from c in db.Customers
select new
{
    c.Customername,
    c. ...,
    Films = from f in c.Films
            select new
            {
                f.FilmName,
                f. ...,
            }
}

这篇关于获取多对多关系中所有实体的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！