Click here to Skip to main content
15,920,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I must first try to develop such that I need inner join between two tables in the database.

It is such that I must retrieve information from venner.from which has brugere.id to be the same time I find out if venner.godkendt is 0

the need to retrieve the name and img of the user table.

I have tried to make it in two different kinds of ways.

var innerjoin = from VenA in db.venners
                        join BrugereA in db.brugeres on VenA.From equals BrugereA.Id

I can best li it here (I can be anywhere like that I made it this way)

var innerjoin = db.venners.GroupBy(venid => venid.To == id && venid.Godkendt == 0);

I have tried to make it plain sql

select brugere.name, brugere.img from venner
INNER JOIN brugere on brugere.id = venner.from WHERE venner.godkendt == 0


EIDT HER


var innerjoin = from VenA in db.venners
                join BrugereA in db.brugeres on VenA.From equals BrugereA.Id
                where VenA.godkendt == 0
                select BrugereA;

        RepeaterVenGodkendt.DataSource = innerjoin;
        RepeaterVenGodkendt.DataBind();


are error on godkendt == 0

CREATE TABLE [dbo].[brugere] (
    [Id]            INT            IDENTITY (1, 1) NOT NULL,
    [brugernavn]    NVARCHAR (255) NOT NULL,
    [adgangskode]   NVARCHAR (255) NOT NULL,
    [fornavn]       NVARCHAR (200) NOT NULL,
    [efternavn]     NVARCHAR (200) NOT NULL,
    [rank]          INT            NOT NULL,
    [img]           NVARCHAR (255) NULL,
    [opretdate]     DATETIME       NOT NULL,
    [point]         INT            NOT NULL,
    [profiltekst]   NTEXT          NULL,
    [nyhedsbrev]    INT            NOT NULL,
    [trydato]       DATETIME       NULL,
    [abonnementsid] INT            NULL,
    [prisid]        INT            NULL,
    [opsagt]        INT            NOT NULL,
    [trytried]      INT            NULL,
    CONSTRAINT [PK_brugere] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[venner] (
    [Id]       INT      IDENTITY (1, 1) NOT NULL,
    [From]     INT      NOT NULL,
    [To]       INT      NOT NULL,
    [DatoTid]  DATETIME NOT NULL,
    [Godkendt] INT      DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Venner] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Venner_ToBrugereFrom] FOREIGN KEY ([From]) REFERENCES [dbo].[brugere] ([Id]),
    CONSTRAINT [FK_Venner_ToBrugereTo] FOREIGN KEY ([To]) REFERENCES [dbo].[brugere] ([Id])
);
Posted
Updated 23-Apr-15 1:17am
v2

1 solution

Your second code isn't a join, it's a grouping :)

I understand you would prefer the Linq-solution? I think this would be what you want:
C#
var innerjoin = from VenA in db.venners
                join BrugereA in db.brugeres on VenA.From equals BrugereA.Id
                where VenA.godkendt == 0
                select new { BrugereA.name, BrugereA.img };

And then you can iterate over the results like so:
C#
foreach (var item in innerjoin)
{
    var name = item.name;  // I used "var" here because I don't know the
    var img = item.img;    // actual types of name and img

    // do something with name and img here
}


Alternatively you can do without the anonymous type and just select BrugereA, like so:
C#
var innerjoin = from VenA in db.venners
                join BrugereA in db.brugeres on VenA.From equals BrugereA.Id
                where VenA.godkendt == 0
                select BrugereA;
 
Share this answer
 
v2
Comments
George Swan 23-Apr-15 5:11am    
I think that the equality test needs to compare properties that have the same name. So you may need to do this:
join BrugereA in db.brugeres on new {Id=VenA.From} equals new {BrugereA.Id}
Also, there is no need to create an anonymous type in the select statement, you can simply select BrugereA
Sascha Lefèvre 23-Apr-15 5:24am    
The equality test definitely doesn't require same property names. You would only introduce an anonymous type if you want to base the join on more than one column.

You're right with your second point - it's not required - but it also doesn't hurt. If he uses some semi smart ORM, it can reduce the amount of data to be transfered. I will mention it as an alternative though.
George Swan 23-Apr-15 8:39am    
Sascha, Thanks for clarifying when anonymous types are required in the equality statement. I take your point about the 'select' statement. Regards, George.
Sascha Lefèvre 23-Apr-15 8:44am    
cheers :)
Regards, Sascha
Jesper Petersen 23-Apr-15 6:46am    
What do I do if I have to have it in a Repeater?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900