CODEWITHSUNDEEP
c#asp.netsqllinq
vinaytejareddyawe
vinaytejareddyawe

Reputation: 11

How to query values associated with the foreign key using linq and display in listview

I have a database and two tables in it Male and Female both has a int primary key with the name ID both the tables have a foreign key and foreign keys of both the tables are in a single separate table named RecommendFemale.

Male Tables:

Male :

[dbo].[Male] (
    [ID]            INT           IDENTITY (1, 1) NOT NULL,
    [Image]         VARCHAR (MAX) NULL,
    [Name]          VARCHAR (50)  NULL,
    [Age]           INT           NULL,
    CONSTRAINT [PK_Female] PRIMARY KEY CLUSTERED ([ID] ASC)
);

Female :

 [dbo].[Female] (
        [ID]            INT           IDENTITY (1, 1) NOT NULL,
        [Image]         VARCHAR (MAX) NULL,
        [Name]          VARCHAR (50)  NULL,
        [Age]           INT           NULL,
        CONSTRAINT [PK_Female] PRIMARY KEY CLUSTERED ([ID] ASC)
    );

Recommend :

[dbo].[RecommendFemale] (
    [ID]          INT IDENTITY (1, 1) NOT NULL,
    [RecommendTo] INT NOT NULL,
    [Recommend]   INT NOT NULL,
    CONSTRAINT [PK_RecommendFemale] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Table_1_Female] FOREIGN KEY ([RecommendTo]) REFERENCES [dbo].[Female] ([ID]),
    CONSTRAINT [FK_Table_1_Male] FOREIGN KEY ([Recommend]) REFERENCES [dbo].[Male] ([ID])
);

RecommendTo is the foreign key for Female tables ID and Recommend is the foreign key value for Male tables ID.

My Question is i want to show the value associated with the foreign key in the list view

Example: Recommend Table has multiple foreign keys values of Female in RecommendTo column and the recommend column list has the values of male table.

i want to show the values associated with the RecommendTo into a listview i.e, the ID 4 has multiple values in foreign key table and i wanna show the values associated with that single number into a list view ,how to do that ?

Upvotes: 1

Views: 1405

Answers (1)

mlurker
mlurker

Reputation: 149

So you want to select all the recommendations for a specific record in [Male] or [Female] table?

If you do not use LINQ TO SQL, but instead execute dynamic query or write stored procedure, it will look like this (find recommendations for record in [Female] table):

SELECT
    m.ID
    ,m.IMAGE
    ,m.NAME
    ,m.AGE
FROM [dbo].[Male] m
INNER JOIN [dbo].[RecommendFemale] rf ON rf.Recommend = m.ID
WHERE ft.RecommendTo = %FEMALE_ID%

Upvotes: 1

Related Questions