C
C#ā€¢6mo ago
nitro

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
nitro
nitroOPā€¢6mo 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;
}
nitro
nitroOPā€¢6mo ago
Genres Table:
No description
nitro
nitroOPā€¢6mo ago
Video Genres Table:
No description
Salman
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo ago
Another important piece of code may be the following:
No description
Salman
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo ago
Bit more context to my DB relations:
No description
nitro
nitroOPā€¢6mo 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
Salmanā€¢6mo ago
so you can simply alter the table, set the relations and then it'd make your life much easier
nitro
nitroOPā€¢6mo ago
But suppose I couldn't? What approach would make it work?
Salman
Salmanā€¢6mo ago
you can, why you couldn't ?
nitro
nitroOPā€¢6mo ago
Or is the current approach simply broken and can not even be referenced properly?
Salman
Salmanā€¢6mo ago
current approach is inefficient and not recommended at all
nitro
nitroOPā€¢6mo 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
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No wonder why I come across stubborn
Salman
Salmanā€¢6mo ago
since it's a study project so you need to look for the recommended and efficient approaches to solve different problems
nitro
nitroOPā€¢6mo ago
So inside my videos table I add a column for a genreId?
Salman
Salmanā€¢6mo ago
right
nitro
nitroOPā€¢6mo ago
That column is then referencing the genre table? and sets the reference on the row with the matching Id?
Salman
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo ago
I still appreciate what you just told me though, wasn't wasted! But now I'm still stuck at step 1
Pobiega
Pobiegaā€¢6mo ago
You can use a three-way join Query the video, then join all genreids that match, then join the genres
cap5lut
cap5lutā€¢6mo 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
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Salmanā€¢6mo 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
cap5lutā€¢6mo 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
Salmanā€¢6mo ago
you can only select the columns you need and skip the others for each table
nitro
nitroOPā€¢6mo ago
How would I alter the GetAll method?
Salman
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No description
nitro
nitroOPā€¢6mo ago
Just getting exceptions
nitro
nitroOPā€¢6mo ago
No description
Salman
Salmanā€¢6mo 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
nitro
nitroOPā€¢6mo ago
I did try to fix a typo and match the actual table and columns
Salman
Salmanā€¢6mo 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
Pobiegaā€¢6mo ago
you need brackets when your names conflict with keywords
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
what exception is that?
nitro
nitroOPā€¢6mo 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 '.'.
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
hmm
nitro
nitroOPā€¢6mo ago
I have a repo on github, if that helps? Maybe I'm not giving enough information
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
Same exception
Salman
Salmanā€¢6mo 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
Pobiegaā€¢6mo ago
ugh, dont wanna spin up a SQL server šŸ˜„
nitro
nitroOPā€¢6mo ago
Again, same šŸ˜›
Salman
Salmanā€¢6mo ago
damn
nitro
nitroOPā€¢6mo ago
No worries, I'm frustrated too
Pobiega
Pobiegaā€¢6mo ago
wait a min
Salman
Salmanā€¢6mo ago
show me your tables in the database
Pobiega
Pobiegaā€¢6mo 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
Salmanā€¢6mo ago
yeah that's better
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No description
nitro
nitroOPā€¢6mo ago
Gotcha
Pobiega
Pobiegaā€¢6mo ago
ah yeah no comma between description and genre.name
nitro
nitroOPā€¢6mo ago
No description
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo 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 ...
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
v not videos you have aliased the table
Salman
Salmanā€¢6mo ago
when using aliases you would need the aliased name in the queries
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
same thing applies to g
nitro
nitroOPā€¢6mo ago
tried that
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
last row your table is called genres not genre you certainly dont need that much brackets btw :p [name], yes the others... no
nitro
nitroOPā€¢6mo ago
Almost there
nitro
nitroOPā€¢6mo ago
No description
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
invalid comma after videos v
nitro
nitroOPā€¢6mo ago
It's working But now I see the redundancy It's getting the value twice?
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
yeah because you have two genres, you get two joined rows
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
here is a visualization of all SQL joins
nitro
nitroOPā€¢6mo ago
Thanks šŸ™‚
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
well yeah
nitro
nitroOPā€¢6mo ago
How would I map the data to only retreive the matching genres?
Pobiega
Pobiegaā€¢6mo ago
take a step back ignore your C# code for now
nitro
nitroOPā€¢6mo ago
Alright
Pobiega
Pobiegaā€¢6mo ago
make this work in SSMS with a single query because this is doable in ONE query
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
well you can fix this with C# and running multiple queries
nitro
nitroOPā€¢6mo ago
What's the C# way?
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
nah dont worry its easy enough to fix we just need to introduce a bit more stuff to your query, namely GROUP BY
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo 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 šŸ˜„
nitro
nitroOPā€¢6mo ago
šŸ˜®
Pobiega
Pobiegaā€¢6mo ago
im sure some SQL genie can fix this even better but it works
nitro
nitroOPā€¢6mo ago
Does my solution seem okay-ish as well? Cause it works..
Pobiega
Pobiegaā€¢6mo ago
No description
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
your code has a huge problem, if you ask me
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
and thats that it uses 2 queries
nitro
nitroOPā€¢6mo ago
Please share
Pobiega
Pobiegaā€¢6mo ago
ie, you first poll the video, then poll the genres
nitro
nitroOPā€¢6mo ago
Yeah query -ception?
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
heard of/worked with LINQ?
nitro
nitroOPā€¢6mo ago
So I can actually understand why it finaly works Heard of yes, worked, probably not
Pobiega
Pobiegaā€¢6mo ago
right its pure C#, but uses the same concepts, even called the same thing
nitro
nitroOPā€¢6mo ago
I think I'll need your help again transforming your query to my tables and the aliases
Pobiega
Pobiegaā€¢6mo ago
how so?
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
Id = (int)reader["id"],
Name = (string)reader["genre"],
Id = (int)reader["id"],
Name = (string)reader["genre"],
there is no field called id
nitro
nitroOPā€¢6mo ago
Do you mean the reader ? Or that it's Id and not id?
Pobiega
Pobiegaā€¢6mo ago
read the exception name and value
Pobiega
Pobiegaā€¢6mo ago
No description
nitro
nitroOPā€¢6mo ago
Yeah I'm lost
Pobiega
Pobiegaā€¢6mo ago
how my dude, how what is an index
nitro
nitroOPā€¢6mo ago
I know what an index is, don't worry.
Pobiega
Pobiegaā€¢6mo ago
what is indexing something look at the parameter in the exception id
nitro
nitroOPā€¢6mo ago
And it's indexing something that's literally not within the same allocated memory block
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
I had no clue it was an issue from the query So we're not grouping any Id
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
This is going to be a long day
Pobiega
Pobiegaā€¢6mo ago
nah man, my summer vacation started 2 hours ago we gucci
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
well, I can highly recommend https://sqlbolt.com/ its an interactive step-by-step sql tutorial very good and free
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
Usually the plane flies right in
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
Hahaha, alright let me have another look, give me a bit though
Pobiega
Pobiegaā€¢6mo ago
and select the ID, and give a name to our genre i'll give you a hint
Pobiega
Pobiegaā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
Do you think there is perhaps a correlation between what we select and what shows up in the results?
nitro
nitroOPā€¢6mo ago
Wait till Easter comes round And yes, I do think there is a correlation ha
Pobiega
Pobiegaā€¢6mo 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#?
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
the genres? yes
nitro
nitroOPā€¢6mo ago
That would differ to yours, right
Pobiega
Pobiegaā€¢6mo ago
only by column names
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
these are my tables as is
nitro
nitroOPā€¢6mo ago
Thanks, that clears it up Just the table bit, so far.
Pobiega
Pobiegaā€¢6mo ago
the names are stupid, I know I copypasted from some random webpage
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
thats really not step 1 but.. yes
nitro
nitroOPā€¢6mo ago
I'm changing around stuff hitting in the dark now. id > video_id Why the hell is it out of range?
Pobiega
Pobiegaā€¢6mo 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#?
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
you dont need to
nitro
nitroOPā€¢6mo ago
Concatenating the selected results into a comma separated string Based on your separator arg
Pobiega
Pobiegaā€¢6mo 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
Pobiegaā€¢6mo ago
it currently has no name
No description
nitro
nitroOPā€¢6mo ago
How does one set a column name in SQL I mean in the query
Pobiega
Pobiegaā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
look at this image closely
nitro
nitroOPā€¢6mo ago
Yeah I do not have the as value part
Pobiega
Pobiegaā€¢6mo ago
right so... what do you think as does?
nitro
nitroOPā€¢6mo ago
sets the title to the value of the column Sorry.
Pobiega
Pobiegaā€¢6mo ago
it gives the column a name, yes
nitro
nitroOPā€¢6mo ago
Sets the column header for our title
Pobiega
Pobiegaā€¢6mo 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?
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
yes! okay, so now our result contains 2 things title and testing but we also want the video id, right?
nitro
nitroOPā€¢6mo ago
Yes
Pobiega
Pobiegaā€¢6mo ago
right
nitro
nitroOPā€¢6mo ago
Executing this results in an exception still, by the way Not sure if that's expected right now :p
Pobiega
Pobiegaā€¢6mo ago
I dont care. because your C# code is busted
nitro
nitroOPā€¢6mo ago
Well my issue is It's been executing in C# Instead of testing in MSSMS
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo ago
well thats just silly.
nitro
nitroOPā€¢6mo ago
Cause this would have made me feel a lot better Lol
Pobiega
Pobiegaā€¢6mo ago
Im pretty sure I told you to ignore C# for now and get it working in SQL first :p
nitro
nitroOPā€¢6mo ago
I blame myself, don't worry Yeah, 8 hours of work already done
Pobiega
Pobiegaā€¢6mo ago
okay so look at the results tab down there only 2 things we need a third what do we do?
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
Noice!
No description
Pobiega
Pobiegaā€¢6mo ago
okay, so far so good how about adding the id?
nitro
nitroOPā€¢6mo ago
Wait
Pobiega
Pobiegaā€¢6mo ago
since, y'know, we need that
nitro
nitroOPā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No description
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No description
nitro
nitroOPā€¢6mo 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
Pobiegaā€¢6mo ago
like... ['Family', 'Horror']?
nitro
nitroOPā€¢6mo ago
Yes
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo ago
No that's good enough for me information-wise. Must be faster iterating over the string and pushing it into the list
Pobiega
Pobiegaā€¢6mo 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
nitro
nitroOPā€¢6mo 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.
Want results from more Discord servers?
Add your server