ASP.NET MVC 5 - LINQ Query to Select Data from Database

Question

I'm working on a school project in ASP.NET MVC 5. The project is about creating a social network. After the user logs in, he will see all public posts on his newsfeed. I am having issues, though, in showing the public posts' data from the database.

This is the script of the database :

create table Utilizador(
    id_utilizador       integer     not null    identity(1,1),
    nome                varchar(50) not null,
    apelido             varchar(50) not null,
    username            varchar(15) not null    unique,
    pass                varchar(50) not null,
    email               varchar(50) not null    unique,
    sexo                char(1)     not null CHECK (sexo IN('M', 'F')),
    país                varchar(50) not null,
    imagem_perfil       varchar(50) not null,
    data_nascimento     date        not null,
    estado              int         not null default 2, --0->Bloqueado 1-Activo, 2-por activar
    primary key (id_utilizador),
    check (email LIKE '%@%.%')
    )

    create table Post(
    id_post         integer         not null identity(1,1),
    texto           varchar(400)    not null,
    primary key(id_post)
    )

    create table Publish_Post(
    id_post         integer         not null,
    id_utilizador   integer         not null,
    data            timestamp       not null,
    primary key(id_post),
    foreign key(id_post) references Post(id_post),
    foreign key(id_utilizador) references Utilizador(id_utilizador)
    )

    create table Privacy(
    id_privacidade  integer     not null identity(1,1), --> 1 public, 2 private
    nome            varchar(50) not null,
    primary key(id_privacidade)
    )

    create table Have_Privacy(
    id_post         integer     not null,
    id_privacidade  integer     not null,
    primary key(id_post),
    foreign key(id_post) references Post(id_post),
    foreign key(id_privacidade) references Privacidade(id_privacidade)
    )

Let me explain why I create the database the way I do: The user creates and publishes some posts that have will have a privacy value (1 or 2). After the user logs in, all public posts(1) should appear on his newsfeed. So far I have this LINQ query in C#:

var id_posts = from p in db.Posts
                           select p.texto;

            ViewBag.Posts = id_posts;

Can someone help me? Thanks in advance :)

Answer 1

Just do this

var id_posts = from p in db.Posts
           join hp in db.Have_Privacy on p.id_post equals hp.id_post
           join prv in db.Privacy on hp.id_privacidade equals prv.id_privacidade
           where prv.nome = 'Private'
           select p.texto;

Tell how it goes

Answer 2

Why not just add a field in Post called isprivate with boolean type of BIT that determines if it's private or not and then use query for provided data with where clause:

var id_posts = from p in db.Posts
                       where isprivate == false
                       select p.texto;

If you want to have more than 2 types of privacy and just stick with DB schema you provided, you can go with a JOIN:

If id decides it is private:

var id_posts = from p in db.Posts
           join hp in db.Have_Privacy on p.id_post equals hp.id_post
           where hp.id_privacidade = 1
           select p.texto;

If name decides it is private:

var id_posts = from p in db.Posts
           join hp in db.Have_Privacy on p.id_post equals hp.id_post
           join prv in db.Privacy on hp.id_privacidade equals prv.id_privacidade
           where prv.nome = 'Private'
           select p.texto;

Also please note that naming tables in one language and columns in other is considered as bad design. It's hard for others (in this example me) to read it, even if I know what it should mean. Two last queries use your schema with no changes implemented.