entity relationship issue

Question

User-1471881183 posted

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

tlkpTitle

now, 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

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

Answer 1

User-1471881183 posted

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

second node

Answer 2

User-1471881183 posted

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