Wrox Programmer Forums
|
BOOK: Beginning ASP.NET 4.5.1 : in C# and VB
This is the forum to discuss the Wrox book Beginning ASP.NET 4.5.1: in C# and VB by Imar Spaanjaars; ISBN: 978-1-118-84677-3
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 4.5.1 : in C# and VB section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 2nd, 2018, 04:10 PM
Authorized User
 
Join Date: Mar 2017
Posts: 55
Thanks: 12
Thanked 0 Times in 0 Posts
Default Chapter 14 - Mapping Data Model (page 489)

Let's say for some reason, I have not established any data diagrams in SQL to join tables together, however I do have foreign keys established in certain tables. For example: I have GenreId in the Reviews table as a foreign key, but have not created a Diagram for the Genre & Reviews tables.

When I create an Entity Data Model in Visual Studio, in this case, these 2 tables will not be joined together. Which option(s) below is(are) possible:
  • Join the 2 tables together in the data model using Visual Studio
  • create meaningful joins via LINQ statements when I need them
  • No choice but to go back to SQL and create the diagram
 
Old April 3rd, 2018, 06:40 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

You don't need to actually have a saved diagram; you just need properly set up foreign keys. That's easily done with the diagrams, but you can also do it through SQL code. Are you sure your foreign keys are correct? If you do create a diagram, do they show up there?

>> Join the 2 tables together in the data model using Visual Studio

Not when using Database First. You could use Code First but that would be a completely different mechanism

>> create meaningful joins via LINQ statements when I need them

I don;t think that'll work if your database isn't set up for it,

>> No choice but to go back to SQL and create the diagram

As per above, no need for an actual diagram, but you need proper relationships.


Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old April 3rd, 2018, 07:47 AM
Authorized User
 
Join Date: Mar 2017
Posts: 55
Thanks: 12
Thanked 0 Times in 0 Posts
Default

I have proper foreign keys established, but when I create the Data Model, the 2 tables are not connected in the .edmx file. Are you saying that's okay? If it's okay, why would I EVER need to create a diagram in SQL?
 
Old April 3rd, 2018, 09:10 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

>>Are you saying that's okay? Not at all

If you have properly setup your relationships between the tables, they should show up in EF as linked tables, regardless of the presence of a diagram. Apparently your references are off, or EF would see them. How did you create them? Here's an example for two (random) tables I created:

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Genre SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Review ADD CONSTRAINT
FK_Review_Genre FOREIGN KEY
(
GenreId
) REFERENCES dbo.Genre
(
Id
) ON UPDATE NO ACTION
ON DELETE NO ACTION

GO
ALTER TABLE dbo.Review SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old April 3rd, 2018, 10:14 AM
Authorized User
 
Join Date: Mar 2017
Posts: 55
Thanks: 12
Thanked 0 Times in 0 Posts
Default

I created the table, then later created the foreign key via constraints.

Example:
  • tb_DecodeHeaders (PK: DecodeNumber)
  • tb_Decode (PK: DecodeID; FK: DecodeNumber)
  • tb_Equip_Armor (PK: ArmorID; FK: DecodeID)

I manually created the tables above, then altered the last 2 tables with foreign key constraints.

I originally did NOT have a DecodeID in the tb_Decode, but rather 2 primary keys (DecodeNumber, DecodeValue), but then I manually added a DecodeID primary key instead.

Before I did this, however, joins always worked out (see below example)
I have many tables referencing the tb_Decode and I simply joined to the table with a WHERE clause that separated out the header.

NOTE: Maybe it's better to have a separate tb_ArmorCategory table, but I have so many categories that I put them all in the Decode table instead.

Example:

Code:
SELECT 
               ArmorName,
               (SELECT DecodeDescription 
                 FROM tb_Decode d inner join tb_Equip_Armor a 
                 ON d.DecodeValue = a.ArmorCategory 
                 WHERE d.DecodeNumber = 4) AS ArmorCategory
                FROM tb_Equip_Armor
That was BEFORE I added the DecodeID as a PK instead.
=================================
When I select SCRIPT PROCEDURE AS CREATE TABLE in SQL, I get the code below (even though I manually created the table and later added the foreign key constraint). Similar scripting exists for the Armor Table (with the ArmorCategory as a foreign key linking to the tb_Decode (DecodeID) column).

However, I did not add any diagram. And when I created the Data model, these 3 tables were not joined together in the graphic display (ie: no arrow showing any relationship).

What part of your scripting example should I have added to mine to make this happen?

Code:
/****** Object:  Table [dbo].[tb_Decode]    Script Date: 4/3/2018 8:54:22 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tb_Decode](
	[DecodeID] [int] IDENTITY(1,1) NOT NULL,
	[DecodeNumber] [int] NOT NULL,
	[DecodeValue] [nchar](10) NOT NULL,
	[DecodeDescription] [nvarchar](50) NULL,
	[DecodeSort] [int] NULL,
 CONSTRAINT [PK_tb_Decode_1] PRIMARY KEY CLUSTERED 
(
	[DecodeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tb_Decode]  WITH CHECK ADD  CONSTRAINT [FK_tb_Decode_tb_DecodeHeaders] FOREIGN KEY([DecodeNumber])
REFERENCES [dbo].[tb_DecodeHeaders] ([DecodeNumber])
GO

ALTER TABLE [dbo].[tb_Decode] CHECK CONSTRAINT [FK_tb_Decode_tb_DecodeHeaders]
GO
 
Old April 3rd, 2018, 10:26 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

>> Before I did this, however, joins always worked out (see below example)

You don't need to have a relationship between two tables to create a join. You can create a SQL statement that joins arbitrary tables and columns without a problem.

However, for EF you do need them to create the navigation properties. Without a proper relationship in the DB, EF can't generate, say, a Genre property on a Review.

Not sure what's missing as I only see part of your schema. I would create the joins using the diagram designer and then compare the resulting SQL with your existing code.
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
papadan (April 3rd, 2018)
 
Old April 3rd, 2018, 10:37 AM
Authorized User
 
Join Date: Mar 2017
Posts: 55
Thanks: 12
Thanked 0 Times in 0 Posts
Default

Okay, after I created the foreign keys and the primary keys - I see now that the Data Model DOES connect the tables (without me having created an SQL Diagram).

Whew - had me scared there for a bit! All is well now! I didn't have the foreign keys and primary keys established the first time around - I see that now.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Mapping Data Model, Ch 14 RAFish0404 BOOK: Beginning ASP.NET 4.5.1 : in C# and VB 1 January 2nd, 2017 10:44 AM
Chapter 14 A simple model binding application hansheung BOOK: Beginning ASP.NET 4.5.1 : in C# and VB 2 November 22nd, 2016 05:42 AM
Chapter 14 : Mapping data model to an object model robochrish BOOK: Beginning ASP.NET 4 : in C# and VB 8 March 5th, 2013 09:52 AM
Chapter 14: Entity Model Code WILL NOT WORK! pittfurg BOOK: Beginning ASP.NET 4 : in C# and VB 6 February 28th, 2013 07:48 AM
Chapter 14 - ADO.NET Entity Data Model Sean1980 BOOK: Beginning ASP.NET 4 : in C# and VB 3 July 15th, 2011 12:24 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.