entity relationship issue

Category: sql server dotnet

Question

winseelan on Tue, 12 Jun 2018 14:31:11


hello all,

i have a web api project using Entity data method of Generate model from database

my doubt is simple but, i have to explain in detail so, that you may understand my problem. below is my sql script

USE [MyDatabase]
GO
/****** Object:  Table [dbo].[tblEmployee]    Script Date: 12/06/18 2:58:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblEmployee](
	[employeeId] [int] IDENTITY(1,1) NOT NULL,
	[employeeName] [nvarchar](200) NOT NULL,
	[windowsUserName] [nvarchar](100) NOT NULL,
	[emailId] [nvarchar](200) NOT NULL,
	[addDate] [datetime] NOT NULL CONSTRAINT [DF_tblEmployee_addDate]  DEFAULT (getdate()),
	[chgDate] [datetime] NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
	[employeeId] 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
/****** Object:  Table [dbo].[tlkpTitle]    Script Date: 12/06/18 2:58:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tlkpTitle](
	[titleId] [tinyint] IDENTITY(1,1) NOT NULL,
	[name] [char](10) NOT NULL,
	[shortCode] [varchar](10) NOT NULL,
	[addDate] [datetime] NOT NULL CONSTRAINT [DF_tlkpTitle_addDate]  DEFAULT (getdate()),
	[addUserId] [int] NOT NULL,
	[chgDate] [datetime] NULL,
	[chgUserId] [int] NULL,
	[isDeleted] [bit] NOT NULL CONSTRAINT [DF_tlkpTitle_isDeleted]  DEFAULT ((0)),
 CONSTRAINT [PK_tlkpTitle] PRIMARY KEY CLUSTERED 
(
	[titleId] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tlkpTitle]  WITH CHECK ADD  CONSTRAINT [FK_tlkpTitle_tblEmployee_AddUserId] FOREIGN KEY([addUserId])
REFERENCES [dbo].[tblEmployee] ([employeeId])
GO
ALTER TABLE [dbo].[tlkpTitle] CHECK CONSTRAINT [FK_tlkpTitle_tblEmployee_AddUserId]
GO

now i have loaded data like below into above 2 tables   

tblEmployee

tblEmployee

tlkpTitle

tblTitlenow, from visual studio, i updated edmx file, now it has all the tables and relationships

then i have created a controller for tlkpTitle so, it looks like below

public class LookUpTitlesController : ApiController
    {
        private EntitiesConnectionString db = new EntitiesConnectionString();

        // GET: api/LookUpTitles
        [Route("api/GetAllTitles")]
        public IQueryable<tlkpTitle> GettlkpTitles()
        {
            return db.tlkpTitles;
        }

        // GET: api/LookUpTitles/5
        [ResponseType(typeof(tlkpTitle))]
        public IHttpActionResult GettlkpTitle(byte id)
        {
            tlkpTitle tlkpTitle = db.tlkpTitles.Find(id);
            if (tlkpTitle == null)
            {
                return NotFound();
            }

            return Ok(tlkpTitle);
        }
..........
..........
}

now, when i call the api http://localhost:50190/api/GetAllTitles im getting data of first title(only one row), for rest of the row its showing $ref so, the output is not correct. please refer below output screenshot

jsonOutput

i could understand that, its problem with foregin key relation ship because, first titleId is 1 and addUserId is 1; this is how the Entity framework understand but, this is wrong. can you please help me to resolve the situation

thanks

Jey



Replies

winseelan on Tue, 12 Jun 2018 16:38:47


hello all,

i got a small clue just now, added below line in Global.asax.cs

GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
            GlobalConfiguration.Configuration.Formatters.Remove(GlobalConfiguration.Configuration.Formatters.XmlFormatter);

now the output seems below but, i dont know why its repeating all data in each node. can you please help me to stop repeating the data in each node

First Node

second node

second node

Olaf Helper on Wed, 13 Jun 2018 09:18:15


Hello,

That's nothing SQL Server related, better post your question to a WebApi related forum

winseelan on Wed, 13 Jun 2018 10:09:08


i found the solution and its here https://code.msdn.microsoft.com/Loop-Reference-handling-in-caaffaf7