SQL Linq, concepts of writing queries

jkirkerx

So I got my experimental code in VB working now, and a basic query. So I'm looking at my original code, in which I sort of pieced together a query. e.g.

Dim query As String = _
"SELECT * FROM MOVIEINFO "

Select Case p_Type
Case "FLV"
query += " WHERE flv = 1 "
Case "H264"
query += " WHERE h264 = 1 "
End Select

Select Case p_Sort
Case "NAME"
query += " ORDER BY MovieName "
Case "TYPE"
query += " ORDER BY MovieType "
End Select

Now I'm seeing if I can do the same thing in Linq So this is my simple query. I'm not quite sure how to sort of do the same thing in the sample below. I'm aware that I can load all the records into pResults, and then do another query to further isolate the results, but that doesn't seem efficient. I really just want to get the records of type, and a start and stop range. In other words, this is a indexed page of movies, and you click the page number to show those results. I already have the start and stop values. Any input that is sound would be appreciated, Thanks

pResults =
(From item In context.Movies
Where item.flv = True
Order By item.MovieName
Select New MovieItem With
{
.MovieID = item.MovieID,
.MovieName = item.MovieName,
.MovieType = item.MovieType,
.MovieDeliveryType = item.MovieDeliveryType,
.MoviePath = item.MoviePath,
.MovieUrl = item.MovieURL,
.MoviePostage = item.MoviePostage,
.MovieThumbnail = item.MovieThumbnail,
.MovieLarge = item.MovieLarge,
.MoviePlaceHolder = item.MoviePlaceHolder,
.MovieBitRate = item.MovieBitRate,
.MovieTime = item.MovieTime,
.MovieSize = item.MovieSize,
.MovieDescription = item.MovieDescription,
.MovieCount = item.MovieCount,
.MovieContributor = item.MovieContributor,
.MoviePostDate = item.MoviePostDate,
.MovieIcon = item.MovieIcon,
.MovieParameters = item.movie_Parameters
}).AsEnumerable

Richard Deeming

Something like this should work:

Dim movies As IQueryable(Of Movie) = context.Movies

' Filter by type:
Select Case p_Type
Case "FLV"
movies = movies.Where(Function(m) m.flv)
Case "H264"
movies = movies.Where(Function(m) m.h264)
End Select

' Sort in the desired order:
Select Case p_Sort
Case "NAME"
movies = movies.OrderBy(Function(m) m.MovieName)
Case "TYPE"
movies = movies.OrderBy(Function(m) m.MovieType)
End Select

' Get a single page of data:
movies = movies.Skip(pageIndex * pageSize).Take(pageSize)

' Project the results:
pResults = movies.AsEnumerable().Select(Function(item) New MovieItem With
{
.MovieID = item.MovieID,
.MovieName = item.MovieName,
.MovieType = item.MovieType,
.MovieDeliveryType = item.MovieDeliveryType,
.MoviePath = item.MoviePath,
.MovieUrl = item.MovieURL,
.MoviePostage = item.MoviePostage,
.MovieThumbnail = item.MovieThumbnail,
.MovieLarge = item.MovieLarge,
.MoviePlaceHolder = item.MoviePlaceHolder,
.MovieBitRate = item.MovieBitRate,
.MovieTime = item.MovieTime,
.MovieSize = item.MovieSize,
.MovieDescription = item.MovieDescription,
.MovieCount = item.MovieCount,
.MovieContributor = item.MovieContributor,
.MoviePostDate = item.MoviePostDate,
.MovieIcon = item.MovieIcon,
.MovieParameters = item.movie_Parameters
})

Assuming MovieItem isn't an entity type, you'll need the AsEnumerable call before the Select call. Everything prior to the AsEnumerable call will be translated into a SQL query.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

That's Radical Man! It works like a charm. I figured out that Movies was my Model, and imported the Namespace for it. So I did some tweaks to get the record selection right, records displayed. I'm still stuck on using the context, instead of Dimensioning it, but the base of what I project into gets disposed before I can generate the HTML. So that's another subject. This SQL Linq is quite interesting, and seems to offer everything hand typed queries offer. It's fast. I'm stoked on my Data Access Layer DLL, Seems to be working. I know the model works now. If I can convert my asp.net webforms app program over to this, and be able to automatically create a database, and seed it with default values, and then if I change the database in one spot, and I can propagate the changes throughout the program, then I'm done. All I have to do is adopt the BootStrap CSS Concept used in MVC, and I can sell this thing finally. Thanks for taking the time to school me on this. I owe you the price of tuition for it. - Street Outlaws And Here's what I got.

Public Shared Function load_index_array( _
ByVal pType As MovieType,
ByVal pSortOrder As SortOrder,
ByVal pPageIndex As Integer,
ByVal pPageSize As Integer,
ByRef pResults As IEnumerable(Of MovieItem)) As Integer

    Dim pValue As Integer = 0

    Dim context As New MoviesContext()
    Dim movies As IQueryable(Of Movies) = context.Movies

    'Sort Order
    Select Case pSortOrder
        Case SortOrder.Name
            movies = movies.OrderBy(Function(m) m.MovieName)
        Case SortOrder.Type
            movies = movies.OrderBy(Function(m) m.MovieType)
        Case SortOrder.Time
            movies = movies.OrderBy(Function(m) m.MovieTime)
        Case SortOrder.Lastest
            movies = movies.OrderByDescending(Function(m) m.MoviePostDate)
        Case SortOrder.Early
            movies = movies.OrderBy(Function(m) m.MoviePostDate)
    End Select

    'Filter By Type
    Select Case pType
        Case MovieType.FLV
            movies = movies.Where(Function(m) m.flv)
        Case MovieType.H264
            movies = movies.Where(Function(m) m.h264)
    End Select

    'Get a Single Page of Data
    If (pPageIndex = 1) Then
        movies = movies.Take(pPageSize)
    Else
        movies = movies.Skip((pPageIndex - 1) \* pPageSize).Take(pPageSize)
    End I