locked
entity relationship issue RRS feed

  • 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

    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

    Tuesday, June 12, 2018 2:33 PM

Answers

All replies

  • 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

    First Node

    second node

    second node

    Tuesday, June 12, 2018 4:39 PM
  • User-1471881183 posted

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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 13, 2018 10:07 AM