The question is the following. There are 2 tables: ROUTE: enter image description here Sight: enter image description here

Each route includes several attractions. In theory, it is necessary to create a one-to-many connection, but with such a connection in the Route table, the coordinates for each attraction will be repeated. How to do? enter image description here

Created 3 table. It turned out the following: enter image description here

Please tell me how to make a selection using LINQ of the inquiry so that I can get a collection of the following plan: (Coordinates of the route and coordinates of the points of the beginning of the route) - from 1 table (Name, description, coordinates of attraction) - from 2 tables

  • one
    If one point of interest can enter several routes and several points of interest in the route, then we have many-to-many connections and therefore we need to create a separate table for such connections (and keep only 2 columns in it with the id of the other) - Mike

3 answers 3

In your case, the connection is many to many.

  1. Separate table for routes.
  2. Separate table for attractions.
  3. Separate table for communication routes and attractions.

    A many-to-many relationship is a list of one element from another. According to the first normal form, the list cannot be in a row, only in a column. Therefore, in a normalized database, many-to-many are realized only through the third binding table (by the way, it does not need to enter a surrogate key, but rather use the natural composite of the keys of both tables).

    But some DBMSs allow breaking the first normal form by introducing a special type "list", "array" or "set". There is no such type in MS SQL, but there are two workarounds:

    1. If, for example, the number of points of interest in the route is limited from above by some small number, then you can make columns ID1, ID2, ..., IDn for each route point in the route table

    2. The list can be emulated by serializing it into VARCHAR (MAX)

    But, I repeat, both of these methods violate the first normal form, as a result of which the task of finding all the routes containing a point of interest is greatly complicated.

    So personally, I recommend using an additional table. It, by the way, can later be supplemented by adding there, for example, the order and the estimated travel time between adjacent points.

      Attraction namespace WindowsFormsApp1 { using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.Spatial; [Table("Attraction")] public partial class Attraction { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Attraction() { RouteAttraction = new HashSet<RouteAttraction>(); } public int id { get; set; } [Required] [StringLength(30)] public string Name { get; set; } [Required] public string Description { get; set; } [Required] public DbGeography Coordinate { get; set; } [DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string CoordinateOGC { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<RouteAttraction> RouteAttraction { get; set; } } }

      Route

       namespace WindowsFormsApp1 { using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.Spatial; [Table("Route")] public partial class Route { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Route() { RouteAttraction = new HashSet<RouteAttraction>(); } public int ID { get; set; } [Required] public DbGeography Coordinates { get; set; } [DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string CoordinatesOGC { get; set; } [Required] public DbGeography CoordinatesStartingPointsRoute { get; set; } [DatabaseGenerated(DatabaseGeneratedOption.Computed)] public string CoordinatesStartingPointsRouteOGC { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<RouteAttraction> RouteAttraction { get; set; } } }

      RouteAttraction

       namespace WindowsFormsApp1 { using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.Spatial; [Table("RouteAttraction")] public partial class RouteAttraction { public int Id { get; set; } public int RouteID { get; set; } public int AttractionID { get; set; } public virtual Attraction Attraction { get; set; } public virtual Route Route { get; set; } } }

      Model

       namespace WindowsFormsApp1 { using System; using System.Data.Entity; using System.ComponentModel.DataAnnotations.Schema; using System.Linq; public partial class Model3 : DbContext { public Model3() : base("name=Model3") { } public virtual DbSet<Attraction> Attraction { get; set; } public virtual DbSet<Route> Route { get; set; } public virtual DbSet<RouteAttraction> RouteAttraction { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Attraction>() .HasMany(e => e.RouteAttraction) .WithRequired(e => e.Attraction) .WillCascadeOnDelete(false); modelBuilder.Entity<Route>() .HasMany(e => e.RouteAttraction) .WithRequired(e => e.Route) .WillCascadeOnDelete(false); } } }

      Collection of view: Route (CoordinatesOGC-> CoordinatesStartingPointsRouteOGC) Attraction (-> Name-> Description-> CoordinateOGC)