C#•5mo 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•5mo 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•5mo ago

Genres Table:

nitroOP•5mo ago

Video Genres Table:

Salman•5mo 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•5mo 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•5mo 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•5mo ago

Another important piece of code may be the following:

Salman•5mo 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•5mo ago

Bit more context to my DB relations:

nitroOP•5mo 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•5mo ago

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

nitroOP•5mo ago

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

Salman•5mo ago

you can, why you couldn't ?

nitroOP•5mo ago

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

Salman•5mo ago

current approach is inefficient and not recommended at all

nitroOP•5mo 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•5mo 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•5mo ago

No wonder why I come across stubborn

Salman•5mo ago

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

nitroOP•5mo ago

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

Salman•5mo ago

right

nitroOP•5mo ago

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

Salman•5mo 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•5mo 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•5mo 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•5mo ago

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

Pobiega•5mo ago

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

cap5lut•5mo 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•5mo 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•5mo 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•5mo 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•5mo 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•5mo ago

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

nitroOP•5mo ago

How would I alter the GetAll method?

Salman•5mo 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•5mo 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•5mo 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•5mo 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•5mo 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•5mo ago

Just getting exceptions

nitroOP•5mo ago

Salman•5mo 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•5mo ago

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

Salman•5mo 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•5mo ago

you need brackets when your names conflict with keywords

nitroOP•5mo 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•5mo ago

what exception is that?

nitroOP•5mo 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•5mo ago

Pobiega•5mo ago

hmm

nitroOP•5mo ago

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

Pobiega•5mo 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•5mo 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•5mo 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•5mo ago

Same exception

Salman•5mo 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•5mo ago

ugh, dont wanna spin up a SQL server 😄

nitroOP•5mo ago

Again, same 😛

Salman•5mo ago

damn

nitroOP•5mo ago

No worries, I'm frustrated too

Pobiega•5mo ago

wait a min

Salman•5mo ago

show me your tables in the database

Pobiega•5mo 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•5mo ago

yeah that's better

Pobiega•5mo 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•5mo ago

Gotcha

Pobiega•5mo ago

ah yeah no comma between description and genre.name

nitroOP•5mo ago

Pobiega•5mo 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•5mo ago

Pobiega•5mo ago

v not videos you have aliased the table

Salman•5mo ago

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

nitroOP•5mo ago

Pobiega•5mo ago

same thing applies to g

nitroOP•5mo ago

tried that

nitroOP•5mo ago

Pobiega•5mo 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•5mo ago

Almost there

nitroOP•5mo ago

Pobiega•5mo ago

invalid comma after videos v

nitroOP•5mo ago

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

nitroOP•5mo ago

Pobiega•5mo ago

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

nitroOP•5mo 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•5mo ago

here is a visualization of all SQL joins

nitroOP•5mo ago

Thanks 🙂

Pobiega•5mo 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•5mo 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•5mo ago

well yeah

nitroOP•5mo ago

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

Pobiega•5mo ago

take a step back ignore your C# code for now

nitroOP•5mo ago

Alright

Pobiega•5mo ago

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

nitroOP•5mo 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•5mo ago

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

nitroOP•5mo ago

What's the C# way?

Pobiega•5mo 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•5mo 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•5mo 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•5mo 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•5mo 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•5mo ago

😮

Pobiega•5mo ago

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

nitroOP•5mo ago

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

Pobiega•5mo ago

nitroOP•5mo ago

Pobiega•5mo ago

your code has a huge problem, if you ask me

nitroOP•5mo ago

Pobiega•5mo ago

and thats that it uses 2 queries

nitroOP•5mo ago

Please share

Pobiega•5mo ago

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

nitroOP•5mo ago

Yeah query -ception?

Pobiega•5mo 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•5mo 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•5mo ago

heard of/worked with LINQ?

nitroOP•5mo ago

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

Pobiega•5mo ago

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

nitroOP•5mo ago

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

Pobiega•5mo ago

how so?

nitroOP•5mo 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•5mo 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•5mo ago

Pobiega•5mo ago

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

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

there is no field called id

nitroOP•5mo ago

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

Pobiega•5mo ago

read the exception name and value

Pobiega•5mo ago

nitroOP•5mo ago

Yeah I'm lost

Pobiega•5mo ago

how my dude, how what is an index

nitroOP•5mo ago

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

Pobiega•5mo ago

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

nitroOP•5mo ago

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

Pobiega•5mo 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•5mo ago

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

Pobiega•5mo 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•5mo ago

This is going to be a long day

Pobiega•5mo ago

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

nitroOP•5mo 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•5mo ago

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

nitroOP•5mo 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•5mo 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•5mo ago

Usually the plane flies right in

Pobiega•5mo 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•5mo ago

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

Pobiega•5mo ago

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

Pobiega•5mo ago

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

nitroOP•5mo ago

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

Pobiega•5mo 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•5mo 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•5mo ago

the genres? yes

nitroOP•5mo ago

That would differ to yours, right

Pobiega•5mo ago

only by column names

nitroOP•5mo 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•5mo ago

these are my tables as is

nitroOP•5mo ago

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

Pobiega•5mo ago

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

nitroOP•5mo 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•5mo ago

thats really not step 1 but.. yes

nitroOP•5mo ago

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

Pobiega•5mo 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•5mo 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•5mo ago

you dont need to

nitroOP•5mo ago

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

Pobiega•5mo 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•5mo ago

it currently has no name

nitroOP•5mo ago

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

Pobiega•5mo ago

look at this image closely

nitroOP•5mo ago

Yeah I do not have the as value part

Pobiega•5mo ago

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

nitroOP•5mo ago

sets the title to the value of the column Sorry.

Pobiega•5mo ago

it gives the column a name, yes

nitroOP•5mo ago

Sets the column header for our title

Pobiega•5mo 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•5mo 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•5mo ago

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

nitroOP•5mo ago

Yes

Pobiega•5mo ago

right

nitroOP•5mo ago

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

Pobiega•5mo ago

I dont care. because your C# code is busted

nitroOP•5mo ago

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

nitroOP•5mo ago

Pobiega•5mo ago

well thats just silly.

nitroOP•5mo ago

Cause this would have made me feel a lot better Lol

Pobiega•5mo ago

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

nitroOP•5mo ago

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

Pobiega•5mo ago

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

nitroOP•5mo 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•5mo 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•5mo 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•5mo ago

Pobiega•5mo 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•5mo ago

Noice!

Pobiega•5mo ago

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

nitroOP•5mo ago

Wait

Pobiega•5mo ago

since, y'know, we need that

nitroOP•5mo 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•5mo ago

Pobiega•5mo 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•5mo 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•5mo ago

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

nitroOP•5mo ago

Yes

Pobiega•5mo 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•5mo ago

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

Pobiega•5mo 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•5mo 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