C#•4mo ago

Trying to map values to a single DTO

I have three separate tables, one for the videos, one for all possible genres and one that holds the videoID and genreID so I can tell which video has which genre(s). Currently it makes sense that I'm getting all possible genres populated instead of only the ones that are actually for the videoID. How can I solve this properly? GenreRepository.cs:

public IEnumerable<Genre> getAllGenres()
{
    List<Genre> genres = new List<Genre>();

    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using(SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand("SELECT [id],[genre] FROM [dbo].[genres]", connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        genres.Add(new Genre()
                        {
                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],
                        });
                    }
                }
            }

            transaction.Commit();

        }
    }

    return genres;
}

public IEnumerable<Genre> getAllGenres()
{
    List<Genre> genres = new List<Genre>();

    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using(SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand("SELECT [id],[genre] FROM [dbo].[genres]", connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        genres.Add(new Genre()
                        {
                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],
                        });
                    }
                }
            }

            transaction.Commit();

        }
    }

    return genres;
}

213 Replies

nitroOP•4mo ago

VideoRepository.cs:

public IEnumerable<Video> GetAll()
{
    List<Video> videos = new List<Video>();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand("SELECT [id],[title],[duration],[image_url],[description] FROM [dbo].[videos]", connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        videos.Add(new Video()
                        {
                            Id = (int)reader["id"],
                            Title = (string)reader["title"],
                            Duration = (int)reader["duration"],
                            ImageUrl = (string)reader["image_url"],
                            Description = (string)reader["description"]
                        });
                    }
                }
            }

            transaction.Commit();
        }
    }

    return videos;
}

public IEnumerable<Video> GetAll()
{
    List<Video> videos = new List<Video>();

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand("SELECT [id],[title],[duration],[image_url],[description] FROM [dbo].[videos]", connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        videos.Add(new Video()
                        {
                            Id = (int)reader["id"],
                            Title = (string)reader["title"],
                            Duration = (int)reader["duration"],
                            ImageUrl = (string)reader["image_url"],
                            Description = (string)reader["description"]
                        });
                    }
                }
            }

            transaction.Commit();
        }
    }

    return videos;
}

nitroOP•4mo ago

Genres Table:

nitroOP•4mo ago

Video Genres Table:

Salman•4mo ago

one that holds the videoID and genreID so I can tell which video has which genre(s).

You don't need this table. You can achieve this using Relations

nitroOP•4mo ago

I understand, but it was set up in a way that I shouldn't alter for now as I need to work with it as it is table-wise.

Salman•4mo ago

you can create a foreign key in the Videos table i.e GenreId and a navigation property Genre then you can access the Genre of the video

nitroOP•4mo ago

Another important piece of code may be the following:

Salman•4mo ago

see this is ineffecient, you're fetching all genres and then finding the one...instead you can have a ForeignKey through which you can get the specific genre of the Video in constant time which would be much faster

nitroOP•4mo ago

Bit more context to my DB relations:

nitroOP•4mo ago

Yeah, and it's not even going to work properly as I'll be filling the list with a different kind of value if it isn't a match

Salman•4mo ago

so you can simply alter the table, set the relations and then it'd make your life much easier

nitroOP•4mo ago

But suppose I couldn't? What approach would make it work?

Salman•4mo ago

you can, why you couldn't ?

nitroOP•4mo ago

Or is the current approach simply broken and can not even be referenced properly?

Salman•4mo ago

current approach is inefficient and not recommended at all

nitroOP•4mo ago

I'm basically working on a study project that got set up for me (not school work, don't worry). But since the idea is to learn about .NET and C#, the databases got set up for me. And a little basic code layer was set, from there I am expanding I'm under the impression that there is already a reference inside the tables towards the ID of both video and genre

Salman•4mo ago

alter table videos add GenreId Int;

alter table videos add GenreId Int;

then

alter table videos add constraint fk_genre_id
foreign key (genre_id) references genre(id);

alter table videos add constraint fk_genre_id
foreign key (genre_id) references genre(id);

that's it

nitroOP•4mo ago

No wonder why I come across stubborn

Salman•4mo ago

since it's a study project so you need to look for the recommended and efficient approaches to solve different problems

nitroOP•4mo ago

So inside my videos table I add a column for a genreId?

Salman•4mo ago

right

nitroOP•4mo ago

That column is then referencing the genre table? and sets the reference on the row with the matching Id?

Salman•4mo ago

right and then you can use JOIN to only select those videos that have matching genre ids in the genre table or you can use Left Join to select all the videos no matter they have genre or not and you can use Right Join to select all genres , no matter they've videos or not

nitroOP•4mo ago

I'm kind of scratching my head now, because now comes the second question, and maybe it's no issue at all, but alas: What if I want multiple genres for a single video? And something makes me think that that's the reason the tables have been set up in that fashion. So in this case the video_genre table references two identical videoId's but a different genreId, then both of those genres get mapped into the DTO as a List

Salman•4mo ago

yes in case you wanna store multiple genres, you would need a separate table because it'd be a many to many relationship then correct

nitroOP•4mo ago

I still appreciate what you just told me though, wasn't wasted! But now I'm still stuck at step 1

Pobiega•4mo ago

You can use a three-way join Query the video, then join all genreids that match, then join the genres

cap5lut•4mo ago

for an m:n relation neither the videos nor the genres table would have FKs. only a a third thable that links these together (a genreID, a videoID, where both are FKs to the respective tables' PKs and its own PK would be over these two columns) for querying that how u want there are different JOIN variants, depending on ur needs

Salman•4mo ago

can you show your VideoDto you would need a list for genres in that class , and can use Joins to fetch all the genres related to that video. For that you would need to modify the query in your GetAll function of videos repository

nitroOP•4mo ago

namespace BlockBusters.Shared
{
    public class VideoDto
    {
        public string Title { get; set; }

        public string VideoThumbUrl { get; set; }

        public int Duration { get; set; }

        public string Description { get; set; }

        public IEnumerable<GenreDto> Genres { get; set; } // Can have multiple genres for a single video.
    }
}

namespace BlockBusters.Shared
{
    public class VideoDto
    {
        public string Title { get; set; }

        public string VideoThumbUrl { get; set; }

        public int Duration { get; set; }

        public string Description { get; set; }

        public IEnumerable<GenreDto> Genres { get; set; } // Can have multiple genres for a single video.
    }
}

I'm reading the JOIN stuff right now at https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver16 But any extra help meanwhile is appreciated

Salman•4mo ago

SELECT id, title, duration,image_url,description genre.name
FROM videos 
INNER JOIN video_genres  ON generes.id = vg.video_id
INNER JOIN genre  ON vg.genre_id = videos.id;

SELECT id, title, duration,image_url,description genre.name
FROM videos 
INNER JOIN video_genres  ON generes.id = vg.video_id
INNER JOIN genre  ON vg.genre_id = videos.id;

try this maybe ?

cap5lut•4mo ago

JOINS can also be bad instead of doing multiple queries. imagine a video that has a lot of genres, u would basically receive something like the following for JOINS

LoTR1, SomeReleaseDate, SomeOtherInfo, Genre1
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre2
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre3
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre4
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre5
Spiderman, AnotherReleaseDate, AnotherInfo, GenreX
Spiderman, AnotherReleaseDate, AnotherInfo, GenreY
Spiderman, AnotherReleaseDate, AnotherInfo, GenreZ

LoTR1, SomeReleaseDate, SomeOtherInfo, Genre1
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre2
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre3
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre4
LoTR1, SomeReleaseDate, SomeOtherInfo, Genre5
Spiderman, AnotherReleaseDate, AnotherInfo, GenreX
Spiderman, AnotherReleaseDate, AnotherInfo, GenreY
Spiderman, AnotherReleaseDate, AnotherInfo, GenreZ

so u have a lot of redundant data in there

Salman•4mo ago

you can only select the columns you need and skip the others for each table

nitroOP•4mo ago

How would I alter the GetAll method?

Salman•4mo ago

just replace the query and debug how the data in result looks like . and then adjust the mapping to dto according to the structure of data being received

nitroOP•4mo ago

"SELECT [id], [title], [duration],[image_url],[description] [genre.name]\r\nFROM [dbo].[videos] \r\nINNER JOIN [video_genres] ON [genres.id] = vg.video_id\r\nINNER JOIN genre ON vg.genre_id = videos.id;" Is what I have till now, do I not need those brackets at all, and how do I finish that last part? Or is it [genres].[id]?

Salman•4mo ago

you would need to adjust the tables according to your current setup, I've shown it as an example rename to match to your setup

nitroOP•4mo ago

"SELECT id, title, duration,image_url,description genre.name\r\nFROM videos \r\nINNER JOIN video_genres  ON genres.id = vg.video_id\r\nINNER JOIN genre  ON vg.genre_id = videos.id;"

Gives me an error, so I assume I need to change it indeed When do I need to use the square brackets surrounding the key words? Basically coming back to this: Or is it [genres].[id]?

Salman•4mo ago

in normal sql i.e in dbms tools I write sql like that but perhaps in c# you need those brackets etc, I haven't use raw sql in c# much you can try putting brackets and see if it works

nitroOP•4mo ago

Just getting exceptions

nitroOP•4mo ago

Salman•4mo ago

uh pls make sure table names match the tables in your actual database and columns as well oh there's a mistake in the query , my bad

nitroOP•4mo ago

I did try to fix a typo and match the actual table and columns

Salman•4mo ago

it should be perhaps

Inner Join video_genres ON genres.id = video_genres.video_id
Inner Join genre ON video_genres.genred_id = videos.id;

Inner Join video_genres ON genres.id = video_genres.video_id
Inner Join genre ON video_genres.genred_id = videos.id;

instead of vg it should be the name of that third table that's storing the reference to both videos and genres i.e video_genres or whatever the name you've set

Pobiega•4mo ago

you need brackets when your names conflict with keywords

nitroOP•4mo ago

I see Cause I tried the following: "SELECT [id], [title], [duration],[image_url],[description] [genre].[name] FROM [dbo].[videos] Inner Join [dbo].[video_genres] ON [dbo].[genres].[id] = [dbo].[video_genres].[video_id]\r\nInner Join genre ON [video_genres].[genre_id] = [dbo].[videos].[id];" No success, same exception as above.

Pobiega•4mo ago

what exception is that?

nitroOP•4mo ago

While "SELECT [id],[title],[duration],[image_url],[description] FROM [dbo].[videos]" actually retrieves all genres (but not exactly what I want). SqlException: Incorrect syntax near '.'.

nitroOP•4mo ago

Pobiega•4mo ago

hmm

nitroOP•4mo ago

I have a repo on github, if that helps? Maybe I'm not giving enough information

Pobiega•4mo ago

your join doesnt make sense

SELECT [id], [title], [duration],[image_url],[description] [genre].[name]
FROM [dbo].[videos]
Inner Join [dbo].[video_genres] ON [dbo].[genres].[id] = [dbo].[video_genres].[video_id]
Inner Join genre ON [video_genres].[genre_id] = [dbo].[videos].[id]

SELECT [id], [title], [duration],[image_url],[description] [genre].[name]
FROM [dbo].[videos]
Inner Join [dbo].[video_genres] ON [dbo].[genres].[id] = [dbo].[video_genres].[video_id]
Inner Join genre ON [video_genres].[genre_id] = [dbo].[videos].[id]

look at that first join you are querying dbo.videos, but your join doesnt refer to it you cant join on an unrelated table

nitroOP•4mo ago

Then I have no clue how I actually can solve this Cause I'm unfamiliar with SQL I am more familiar with C# than SQL, and I'm not even a beginner IMO.

Pobiega•4mo ago

SELECT [id], [title], [duration],[image_url],[description] [genre].[name]
FROM [videos] v,
INNER JOIN [video_genres] vg ON v.id = vg.[video_id]
INNER JOIN genre g ON g.id = vg.[genre_id]

SELECT [id], [title], [duration],[image_url],[description] [genre].[name]
FROM [videos] v,
INNER JOIN [video_genres] vg ON v.id = vg.[video_id]
INNER JOIN genre g ON g.id = vg.[genre_id]

? select from v, add vg, add g

nitroOP•4mo ago

Same exception

Salman•4mo ago

SELECT [id], [title], [duration],[image_url],[description] [genre].[name]
FROM [dbo].[videos]
Inner Join [dbo].[video_genres] ON [dbo].[videos].[id] = [dbo].[video_genres].[video_id]
Inner Join [dbo].[genres] ON [video_genres].[genre_id] = [dbo].[genres].[id]

SELECT [id], [title], [duration],[image_url],[description] [genre].[name]
FROM [dbo].[videos]
Inner Join [dbo].[video_genres] ON [dbo].[videos].[id] = [dbo].[video_genres].[video_id]
Inner Join [dbo].[genres] ON [video_genres].[genre_id] = [dbo].[genres].[id]

try this

Pobiega•4mo ago

ugh, dont wanna spin up a SQL server 😄

nitroOP•4mo ago

Again, same 😛

Salman•4mo ago

damn

nitroOP•4mo ago

No worries, I'm frustrated too

Pobiega•4mo ago

wait a min

Salman•4mo ago

show me your tables in the database

Pobiega•4mo ago

still the same error about the .? step 1: do the query in SSMS first get it to work there, before doing it from C#

Salman•4mo ago

yeah that's better

Pobiega•4mo ago

cause it seems you have a syntax error somewhere, not a logic error are you SURE its this query? cause I see no syntax errors

nitroOP•4mo ago

Gotcha

Pobiega•4mo ago

ah yeah no comma between description and genre.name

nitroOP•4mo ago

Pobiega•4mo ago

right, so fix the ambigiousness you'll need to prefix the columns with their table alias

SELECT v.id, v.title, v.duration, v.image_url ...

SELECT v.id, v.title, v.duration, v.image_url ...

nitroOP•4mo ago

Pobiega•4mo ago

v not videos you have aliased the table

Salman•4mo ago

when using aliases you would need the aliased name in the queries

nitroOP•4mo ago

Pobiega•4mo ago

same thing applies to g

nitroOP•4mo ago

tried that

nitroOP•4mo ago

Pobiega•4mo ago

last row your table is called genres not genre you certainly dont need that much brackets btw :p [name], yes the others... no

nitroOP•4mo ago

Almost there

nitroOP•4mo ago

Pobiega•4mo ago

invalid comma after videos v

nitroOP•4mo ago

It's working But now I see the redundancy It's getting the value twice?

nitroOP•4mo ago

Pobiega•4mo ago

yeah because you have two genres, you get two joined rows

nitroOP•4mo ago

And I still need to fix the part where it now populates the genres that belong to that video id only Now it's still behaving as previously, I suspect because I haven't touched the return DTO yet

        public IEnumerable<VideoDto> ShowAllVideos()
        {
            return this.videoRepository.GetAll().Select(video =>
            {
                return new VideoDto()
                {
                    Title = video.Title,
                    Description = video.Description,
                    Duration = video.Duration,
                    VideoThumbUrl = video.ImageUrl,
                    Genres = this.genreRepository.getAllGenres().Select(genre =>
                    {
                        return new GenreDto { Genre = genre.Name };
                    }) 
                };
            });
        }

        public IEnumerable<VideoDto> ShowAllVideos()
        {
            return this.videoRepository.GetAll().Select(video =>
            {
                return new VideoDto()
                {
                    Title = video.Title,
                    Description = video.Description,
                    Duration = video.Duration,
                    VideoThumbUrl = video.ImageUrl,
                    Genres = this.genreRepository.getAllGenres().Select(genre =>
                    {
                        return new GenreDto { Genre = genre.Name };
                    }) 
                };
            });
        }

Pobiega•4mo ago

here is a visualization of all SQL joins

nitroOP•4mo ago

Thanks 🙂

Pobiega•4mo ago

so whats happening is that it starts on video, joins in the vg.. but if there is more than one hit, you now have 2 rows and then it joins those rows on g result: 2 mr beans

nitroOP•4mo ago

Yeah but now I altered the table so that mr bean has 2 of the same genres So I want to see Family 2x But it's still showing all genres due to the code I just sent (I think)

Pobiega•4mo ago

well yeah

nitroOP•4mo ago

How would I map the data to only retreive the matching genres?

Pobiega•4mo ago

take a step back ignore your C# code for now

nitroOP•4mo ago

Alright

Pobiega•4mo ago

make this work in SSMS with a single query because this is doable in ONE query

nitroOP•4mo ago

Yeah but the issue is that I have 0 SQL experience I only select top 1000 for work to check data 😛 I'm more confused now than I started off with I assumed it would have been more of C#

Pobiega•4mo ago

well you can fix this with C# and running multiple queries

nitroOP•4mo ago

What's the C# way?

Pobiega•4mo ago

like, fetching all the genres, then fetching all videos and joining their genre associations as keys and then in your mapping looking up the actual names but thats... really bad code

nitroOP•4mo ago

Cause I finally managed to get data from different tables into a single DTO, that felt massive to me. And yeah, that sounds a bit redundant The project was set up for me with the 3 tables And I have no effing clue what to do as I felt like I needed to leave them alone and get it working from the C# (majority). Might need a little break 😛

Pobiega•4mo ago

nah dont worry its easy enough to fix we just need to introduce a bit more stuff to your query, namely GROUP BY

nitroOP•4mo ago

Will you still be here in like 15-20 min? Will have a quick cup of tea and cool-down to try again :p

public IEnumerable<Genre> getAllGenresForVideo(int videoId)
{
    List<Genre> genres = new List<Genre>();

    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using(SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand(@"
        SELECT g.[id], g.[genre] 
        FROM [dbo].[genres] g
        INNER JOIN [dbo].[video_genres] vg ON g.[id] = vg.[genre_id]
        WHERE vg.[video_id] = @videoId", connection, transaction))
            {
                command.Parameters.AddWithValue("@videoId", videoId);
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        genres.Add(new Genre()
                        {
                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],
                        });
                    }
                }
            }

            transaction.Commit();

        }
    }

    return genres;
}

public IEnumerable<Genre> getAllGenresForVideo(int videoId)
{
    List<Genre> genres = new List<Genre>();

    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using(SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand(@"
        SELECT g.[id], g.[genre] 
        FROM [dbo].[genres] g
        INNER JOIN [dbo].[video_genres] vg ON g.[id] = vg.[genre_id]
        WHERE vg.[video_id] = @videoId", connection, transaction))
            {
                command.Parameters.AddWithValue("@videoId", videoId);
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        genres.Add(new Genre()
                        {
                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],
                        });
                    }
                }
            }

            transaction.Commit();

        }
    }

    return genres;
}

Pobiega•4mo ago

select
  m.Title,
  STRING_AGG(g.[Name], ', ')
from
  movies m
  inner join genre_movie mg on m.ID = mg.TitleID
  inner join genres g on g.ID = mg.GroupID
group by
  m.ID,
  m.Title;

select
  m.Title,
  STRING_AGG(g.[Name], ', ')
from
  movies m
  inner join genre_movie mg on m.ID = mg.TitleID
  inner join genres g on g.ID = mg.GroupID
group by
  m.ID,
  m.Title;

ta da replace table/column names as needed 😄

nitroOP•4mo ago

😮

Pobiega•4mo ago

im sure some SQL genie can fix this even better but it works

nitroOP•4mo ago

Does my solution seem okay-ish as well? Cause it works..

Pobiega•4mo ago

nitroOP•4mo ago

Pobiega•4mo ago

your code has a huge problem, if you ask me

nitroOP•4mo ago

Pobiega•4mo ago

and thats that it uses 2 queries

nitroOP•4mo ago

Please share

Pobiega•4mo ago

ie, you first poll the video, then poll the genres

nitroOP•4mo ago

Yeah query -ception?

Pobiega•4mo ago

with two joins and a group, you can do all that work in one query the database is a lot better than we are at fetching data, so doing 1 query instead of 1+n actually, I was understating the problem before its not 2 its 1+n where n is the number of hits from the first query 5 videos? 1+5 100000000 videos? 1+100000000

nitroOP•4mo ago

I agree with you It's just that I understand C# slightly better than SQL :p But I will commit both solutions for references And in the mean time figure out both step by step

Pobiega•4mo ago

heard of/worked with LINQ?

nitroOP•4mo ago

So I can actually understand why it finaly works Heard of yes, worked, probably not

Pobiega•4mo ago

right its pure C#, but uses the same concepts, even called the same thing

nitroOP•4mo ago

I think I'll need your help again transforming your query to my tables and the aliases

Pobiega•4mo ago

how so?

nitroOP•4mo ago

Wait This might be it

select
  m.Title,
  STRING_AGG(g.genre, ', ')
from
  videos m
  inner
join video_genres vg on m.id = vg.id
  inner
join genres g on g.id = vg.id
group by
  m.id,
  m.title;

select
  m.Title,
  STRING_AGG(g.genre, ', ')
from
  videos m
  inner
join video_genres vg on m.id = vg.id
  inner
join genres g on g.id = vg.id
group by
  m.id,
  m.title;

But the group by m.title Why is that part needed?

Pobiega•4mo ago

well, we end the statement by grouping by the nature of groups, we can only use a function over the grouped values, or a value that was part of the group key STRING_AGG joins all the genres in each group together m.Title was added to our key, since we already know it will be the same for each id

nitroOP•4mo ago

Pobiega•4mo ago

                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],

                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],

there is no field called id

nitroOP•4mo ago

Do you mean the reader ? Or that it's Id and not id?

Pobiega•4mo ago

read the exception name and value

Pobiega•4mo ago

nitroOP•4mo ago

Yeah I'm lost

Pobiega•4mo ago

how my dude, how what is an index

nitroOP•4mo ago

I know what an index is, don't worry.

Pobiega•4mo ago

what is indexing something look at the parameter in the exception id

nitroOP•4mo ago

And it's indexing something that's literally not within the same allocated memory block

Pobiega•4mo ago

its lowercased correctamundo again, look at your SQL

select
  m.Title,
  STRING_AGG(g.genre, ', ')
FROM ....

select
  m.Title,
  STRING_AGG(g.genre, ', ')
FROM ....

so we have a field called Title, an unnamed field, and... thats it no field called id

nitroOP•4mo ago

I had no clue it was an issue from the query So we're not grouping any Id

Pobiega•4mo ago

we are not SELECTING any id only the things you select are available in the result and C# can only interact with the result

nitroOP•4mo ago

This is going to be a long day

Pobiega•4mo ago

nah man, my summer vacation started 2 hours ago we gucci

nitroOP•4mo ago

Lmfao You deserved your vacation Long story short; this SQL stuff isn't getting me anywhere as I have never read up the basics of it. Feels like diving deep into Node.js libraries without even understanding JS.

Pobiega•4mo ago

well, I can highly recommend https://sqlbolt.com/ its an interactive step-by-step sql tutorial very good and free

nitroOP•4mo ago

I'll book mark it, but for now I'd like to be spoon-fed a solution 😛 I can't learn too much at once, gets a bit demotivating if one relies on the other and I'm crap at both.

Pobiega•4mo ago

Ive already fetched the spoon, dipped it in the baby food and its now hovering outside your mouth you will need to learn forward and open your mouth

nitroOP•4mo ago

Usually the plane flies right in

Pobiega•4mo ago

ignore sqlbolt for now thou, thats fine 🙂 the plane is hovering my guy you just need to do that tiny last bit of effort

nitroOP•4mo ago

Hahaha, alright let me have another look, give me a bit though

Pobiega•4mo ago

and select the ID, and give a name to our genre i'll give you a hint

Pobiega•4mo ago

Do you think there is perhaps a correlation between what we select and what shows up in the results?

nitroOP•4mo ago

Wait till Easter comes round And yes, I do think there is a correlation ha

Pobiega•4mo ago

cool so, what do we add/change to/in the sql query to also include the movie title, and be able to access the genres from C#?

nitroOP•4mo ago

Ah wait your SQL is first of all is grabbing them by separating by comma's, right? So my STRING_AGG needs changing anyhow

Pobiega•4mo ago

the genres? yes

nitroOP•4mo ago

That would differ to yours, right

Pobiega•4mo ago

only by column names

nitroOP•4mo ago

Yeah, I'm the R-word right now First thought that was your table as-is. Not that it was the already executed query we're discussing now

Pobiega•4mo ago

these are my tables as is

nitroOP•4mo ago

Thanks, that clears it up Just the table bit, so far.

Pobiega•4mo ago

the names are stupid, I know I copypasted from some random webpage

nitroOP•4mo ago

I'm in no position to assign stupid-ness to anyone right now 😄 Step by step: 1. We are selecting m.Title column from videos table (right)?

Pobiega•4mo ago

thats really not step 1 but.. yes

nitroOP•4mo ago

I'm changing around stuff hitting in the dark now. id > video_id Why the hell is it out of range?

Pobiega•4mo ago

select -- note, only 2 things are currently being selected
  m.Title, -- will be called 'Title'
  STRING_AGG(g.[Name], ', ') -- has no name
from
  Movies m
  inner join movie_genre mg on m.Id = mg.MovieId
  inner join Genres g on g.Id = mg.GenreId
group by
  m.Id,
  m.Title;

select -- note, only 2 things are currently being selected
  m.Title, -- will be called 'Title'
  STRING_AGG(g.[Name], ', ') -- has no name
from
  Movies m
  inner join movie_genre mg on m.Id = mg.MovieId
  inner join Genres g on g.Id = mg.GenreId
group by
  m.Id,
  m.Title;

how do we fix STRING_AGG being readable from C#?

nitroOP•4mo ago

I have no clue how STRING_AGG even works in depth Or even surface level Besides that it seems to concatenate in this case

Pobiega•4mo ago

you dont need to

nitroOP•4mo ago

Concatenating the selected results into a comma separated string Based on your separator arg

Pobiega•4mo ago

what it does is join several results into one how it does it, you dont care about what you MUST do, is give it a column name in your result set

Pobiega•4mo ago

it currently has no name

nitroOP•4mo ago

How does one set a column name in SQL I mean in the query

Pobiega•4mo ago

look at this image closely

nitroOP•4mo ago

Yeah I do not have the as value part

Pobiega•4mo ago

right so... what do you think as does?

nitroOP•4mo ago

sets the title to the value of the column Sorry.

Pobiega•4mo ago

it gives the column a name, yes

nitroOP•4mo ago

Sets the column header for our title

Pobiega•4mo ago

yes! so, we are fine with Title as a name for m.Title so we dont need to give it a new one but the STRING_AGG one needs a name... can you show me the updated SQL code?

nitroOP•4mo ago

Bit scared to now 😛

select
  m.title,
  STRING_AGG(g.genre, ', ') as 'testing'
from
  videos m
  inner
join video_genres vg on m.id = vg.id
  inner
join genres g on g.id = vg.id
group by
  m.id,
  m.title;

select
  m.title,
  STRING_AGG(g.genre, ', ') as 'testing'
from
  videos m
  inner
join video_genres vg on m.id = vg.id
  inner
join genres g on g.id = vg.id
group by
  m.id,
  m.title;

Pobiega•4mo ago

yes! okay, so now our result contains 2 things title and testing but we also want the video id, right?

nitroOP•4mo ago

Yes

Pobiega•4mo ago

right

nitroOP•4mo ago

Executing this results in an exception still, by the way Not sure if that's expected right now :p

Pobiega•4mo ago

I dont care. because your C# code is busted

nitroOP•4mo ago

Well my issue is It's been executing in C# Instead of testing in MSSMS

nitroOP•4mo ago

Pobiega•4mo ago

well thats just silly.

nitroOP•4mo ago

Cause this would have made me feel a lot better Lol

Pobiega•4mo ago

Im pretty sure I told you to ignore C# for now and get it working in SQL first :p

nitroOP•4mo ago

I blame myself, don't worry Yeah, 8 hours of work already done

Pobiega•4mo ago

okay so look at the results tab down there only 2 things we need a third what do we do?

nitroOP•4mo ago

We add an extra row with the as value (for the header) on the right side, the left side becomes the column value

Pobiega•4mo ago

you lost me wat I mean, you can make it

select
  'oh man I love cheese' as 'opinion',
  m.title,
  STRING_AGG(g.genre, ', ') as 'testing'...

select
  'oh man I love cheese' as 'opinion',
  m.title,
  STRING_AGG(g.genre, ', ') as 'testing'...

dont think its what you want thou

nitroOP•4mo ago

select
  m.title,
  STRING_AGG(g.genre, ', ') as 'testing',
  m.duration as 'another_header'
from
  videos m
  inner
join video_genres vg on m.id = vg.video_id
  inner
join genres g on g.id = vg.video_id
group by
  m.id,
  m.title,
  m.duration

select
  m.title,
  STRING_AGG(g.genre, ', ') as 'testing',
  m.duration as 'another_header'
from
  videos m
  inner
join video_genres vg on m.id = vg.video_id
  inner
join genres g on g.id = vg.video_id
group by
  m.id,
  m.title,
  m.duration

nitroOP•4mo ago

Pobiega•4mo ago

you don't need the as there you only need as when you are selecting function results anything from an actual data column will default to its column name

nitroOP•4mo ago

Noice!

Pobiega•4mo ago

okay, so far so good how about adding the id?

nitroOP•4mo ago

Wait

Pobiega•4mo ago

since, y'know, we need that

nitroOP•4mo ago

Just trying to see which errors I get when changing the group by values to something that doesnt exist But ok wll do that now

nitroOP•4mo ago

Pobiega•4mo ago

looks good. not sure why you removed the duration, but.. you do you 😄 also, id is by convention always the first thing you select

nitroOP•4mo ago

select
m.id,
  m.title,
  STRING_AGG(g.genre, ', ') as 'genre'
from
  videos m
  inner
join video_genres vg on m.id = vg.video_id
  inner
join genres g on g.id = vg.video_id
group by
  m.id,
  m.title

select
m.id,
  m.title,
  STRING_AGG(g.genre, ', ') as 'genre'
from
  videos m
  inner
join video_genres vg on m.id = vg.video_id
  inner
join genres g on g.id = vg.video_id
group by
  m.id,
  m.title

Now my next question arrises How can I get them into the list instead of a string with the values comma separated?

Pobiega•4mo ago

like... ['Family', 'Horror']?

nitroOP•4mo ago

Yes

Pobiega•4mo ago

you'll need to split it in C# then SQL can't return things results with lists inside a single result honestly, at that point you might as well just not group the results and just iterate over the results as you go

nitroOP•4mo ago

No that's good enough for me information-wise. Must be faster iterating over the string and pushing it into the list

Pobiega•4mo ago

¯\_(ツ)_/¯ no need to iterate and push just... assign Genres = genresFromSqlAsString.Split(", ") also, probably want to change that separator in case a genre ever contains ", " in the name 😄 maybe use a | or something

nitroOP•4mo ago

Yeah I'm lost again Thanks for the help though, appreciate it so far 🙂 I'm going to have dinner and get back to it later.

Gaming

Programming

Trying to map values to a single DTO