I have a WinForms application connected to a Movies
SQL Server database via Entity Framework (EF 6).
I have the following SQL which I'm attempting to convert into a LINQ query
SELECT
t.movieId,m.Title, t.number_of_files
FROM
(SELECT mlf.movieId AS movieId, COUNT(*) AS number_of_files
FROM MovieLinkFile mlf, "file" f
WHERE f.path LIKE 'M:\%'
AND f.id = mlf.fileId
GROUP BY mlf.movieId
HAVING COUNT(*) > 1) t
JOIN
Movie m ON t.movieId = m.Id
ORDER BY
m.title
Here is my attempt at LINQ using LINQPad http://www.linqpad.net/
from m in Movies
from mlf in Movielinkfiles
from f in Files
where m.Id == mlf.MovieId
&& mlf.FileId == Files.id
&& f.path.StartsWith ("M:\\")
.GroupBy(x => x.movieId)
.where(c => c.count() > 1) // invalid expression
select m
The C# code for connecting to the DB looks something like this
private void LoadMovies()
{
TitlesLV.Items.Clear();
using (var context = new MoviesEntities())
{
// get a full list of all movies (will return everything from the database)
var allMovies = context.movies.OrderBy(m => m.title).ToArray();
foreach(var Movie in allMovies)
{
ListViewItem item = new ListViewItem();
item.Text = Movie.title;
item.Tag = Movie;
TitlesLV.Items.Add(item);
}
}
}
In the database there are 3 tables:
- Movies (id, Title, Year, IMDB,etc)
- MoviesLinkFiles (id, MovieId, FileId)
- Files (id, path)
How do I write the LINQ statement in C#?
CodePudding user response:
To get all movies that have more than one file starting with "M:\", use Count
with the Path.StartsWith
expression, and check the result of Count is greater than 1.
var filteredMovies = movies
.Where(movie => movie.Files
.Count(file => file.Path.StartsWith("M:\\")) > 1
)
.OrderBy(movie => movie.Title);