none
How to use Linq decoupled on client side, but get's executed on server side RRS feed

  • Question

  • Hey

    I try to explain my situation here as good as I can

    I want to achieve a complete decoupled scenario where I have a 3 layer situation

    1) Let's call it Server side
    2) Let's call it Exchange
    3) Let's call it Client side

    I am not doing anything complete new here, but I do not find an answer to my working progress...

    The exchange layer is a project, which only holds Interfaces and POCO classes for Data Exchange. 
    For this example here I will talk about an Interface called "Customers".
    This interface "Customers" holds a single function called: "GetCustomers"

    So, the function GetCustomers gives back a list of Customers, which are defined via the POCO class Customer.

    Now on server side I write a class, which implements from the Interface Customers. There I write some specific code, let's say for Ms-SQL, get all my customers from database, generate the return data into the Poco Class Customer and give it back

    On client side, I run for example a button event and fire the function GetCustomers and get back all the data from my server side function

    I think, so far, there is nothing special. And I hope somebody still reads...

    The situation I do have right now: How can I extend the function "GetCustomers" in my Exchange Layer, that I am able to give a where clause to the function, so on server side, the where function can be executed directly on the database?

    Because in the above written scenario, when I run a where clause on the GetCustomers clause, then first all Customers get read, before they get filtered on client side, what makes absolut no sense.

    But I don't wont to write functions for every possible query like: "GetCustomers(LastName as String) As Customer"

    And now I know, that I want to filter all Lastnames. - I want to be able to write: GetCustomers.Where(Function(cust) cust.Lastname = "xxx"

    and this should be executed on server side. - But I don't know, how I can handle this.

    THX for every tipp into this situation

    Have a nice evening

    Saturday, December 29, 2018 7:31 PM

All replies

  • Here is a VB.NET solution using ASP.NET WebAPI service and WebAPI client that is the ASP.NET MVC solution the presentation layer.

    The Data Access Layer (DAL) is using the DAO and DTO patterns with ADO.NET Entity Framework. The DTO travels between processes, like service process/client process. The DTO(s) are sitting in a classlib project called Entities you can call it whatever you like. All projects have reference to Entities and know about the DTO(s) as each project acts upon the DTO if need be as it passes through or is used by a project.

    https://en.wikipedia.org/wiki/Data_transfer_object

    https://www.codeguru.com/vb/gen/vb_misc/oop/article.php/c7063/Data-Transfer-Object-Pattern-Goes-VBNET.htm

    https://www.tutorialspoint.com/design_pattern/data_access_object_pattern.htm

    I recommend that you understand the DAO pattern.

    I also recommend that you use a UI design pattern like MVP that implements seperation of duty and loose coupling at the UI. It shouldn't be that a form event makes a call directly to the service.

    https://www.codeproject.com/Articles/228214/Understanding-Basics-of-UI-Design-Pattern-MVC-MVP

    You should watch all  the shows just so that you kind of understand and not just focus on the VB.NET example.

    http://polymorphicpodcast.com/shows/mv-patterns/

    You may be interested in the below. 

    https://docs.microsoft.com/en-us/previous-versions/msp-n-p/ee658117(v=pandp.10)

    If a method is returning a collection, then you can use Linq on the method and filter.

    dim results = GetCustomers().Where(Function(cust) cust.Lastname = "xxx").ToList()

    HTH

    clinet……..

    Imports Entities
    Namespace WebApi
        Public Interface IWebApi
            Function GetProjsByUserIdApi(userid As String) as List(of DtoProject)
            Function GetProjByIdApi(id As int32) as DtoProject
            sub CreateProjectApi(dto As DtoProject)
            sub  UpdateProjectApi(dto As DtoProject)
            sub DeleteProjectApi(dto As DtoId)
     
       End Interface
    End Namespace

    ======================================================================

    Imports Entities Imports Newtonsoft.Json Namespace WebApi Public Class WebApi Implements IWebApi #Region "Project" public Function GetProjsByUserIdApi(userid As String) as List(of DtoProject) Implements IWebApi.GetProjsByUserIdApi dim dtoprojects = new List(Of DtoProject) dim url = "http://localhost/WebApiVB/api/project/GetProjectsByUserId?userid=" & userid Using webclient As New WebClient dim json = webclient.DownloadString(url) Dim projects = JsonConvert.DeserializeObject(of List(Of DtoProject))(json) dtoprojects = projects End Using Return dtoprojects End Function public Function GetProjByIdApi(id As int32) as DtoProject Implements IWebApi.GetProjByIdApi dim dto as DtoProject dim url = "http://localhost/WebApiVB/api/project/GetProjectById?id=" & id Using webclient As New WebClient dim json = webclient.DownloadString(url) Dim project = JsonConvert.DeserializeObject(of DtoProject)(json) dto = project End Using Return dto End Function public sub CreateProjectApi(dto As DtoProject) Implements IWebApi.CreateProjectApi Dim reqString As byte() Using webclient As New WebClient dim url as string = "http://localhost/WebApiVB/api/project/CreateProject" webClient.Headers("content-type") = "application/json" reqString = Encoding.Default.GetBytes(JsonConvert.SerializeObject(dto, Formatting.Indented)) webClient.UploadData(url, "post", reqString) End Using End sub public sub UpdateProjectApi(dto As DtoProject) Implements IWebApi.UpdateProjectApi Dim reqString As byte() Using webclient As New WebClient dim url as string = "http://localhost/WebApiVB/api/project/UpdateProject" webClient.Headers("content-type") = "application/json" reqString = Encoding.Default.GetBytes(JsonConvert.SerializeObject(dto, Formatting.Indented)) webClient.UploadData(url, "post", reqString) End Using End sub public sub DeleteProjectApi(dto As DtoId) Implements IWebApi.DeleteProjectApi Dim reqString As byte() Using webclient As New WebClient dim url as string = "http://localhost/WebApiVB/api/project/DeleteProject" webClient.Headers("content-type") = "application/json" reqString = Encoding.Default.GetBytes(JsonConvert.SerializeObject(dto, Formatting.Indented)) webClient.UploadData(url, "post", reqString) End Using End sub

    end class

    service...
    Imports System.Web.Http
    Imports DAL
    Imports Entities
    
    Namespace Controllers
    
        <CustomExceptionFilter>
        Public Class ProjectController
            Inherits ApiController
    
            Private ReadOnly _daoproject As IDaoProject
    
            public sub New (daoproject As IDaoProject)
                _daoproject = daoproject
            End sub
    
            <HttpGet>
            <ActionName("GetProjectById")>
            public Function GetProjectById(ByVal id As Int32) As DtoProject
                return _daoproject.GetProjectById(id)
            End Function
    
    
            <HttpGet>
            <ActionName("GetProjectsByUserId")>
            public Function GetProjectsByUserId(ByVal userid As String) As List(Of DtoProject)
                return _daoproject.GetProjectsByUserId(userid)
            End Function
    
            <HttpPost>
            <ActionName("CreateProject")>
            public sub CreateProject(ByVal dto As DtoProject)
                Call _daoproject.CreateProject(dto)
            End sub
            
            <HttpPost>
            <ActionName("UpdateProject")>
            public sub UpdateProject(ByVal dto As DtoProject)
                Call _daoproject.UpdateProject(dto)
            End sub
    
            <HttpPost>
            <ActionName("DeleteProject")>
            public sub  DeleteProject(ByVal dto As DtoId)
                Call _daoproject.DeleteProject(dto.Id)
            End sub
            
        End Class
    End Namespace
    DAL....
    Imports Entities
    
    Public Interface IDaoProject
        Function GetProjectById(ByVal id As Int32) As DtoProject
        Function GetProjectsByUserId(ByVal userid As String) As List(Of DtoProject)
        Sub CreateProject(ByVal dto As DtoProject)
        Sub UpdateProject(ByVal dto As DtoProject)
        Sub DeleteProject(ByVal id As Int32)
    End Interface
    
    =======================================
    
    
    Imports System.Data.Entity
    Imports Entities
    
    Public Class DaoProject
        Implements IDaoProject
    
        Private ReadOnly context As ProjectManagementEntities
    
        public sub New (dbcontext As ProjectManagementEntities)
            context = dbcontext
        End sub
    
        Public Function GetProjectById(ByVal id As Int32) As DtoProject Implements IDaoProject.GetProjectById
    
            Dim dto = New DtoProject()
       
            Dim project = (context.Projects.Where(Function(a) a.ProjectId = id)).SingleOrDefault()
    
            If IsNothing(project) Then
                Return dto
            End If
    
            dto.ProjectId = project.ProjectId
            dto.ClientName = project.ClientName
            dto.ProjectName = project.ProjectName
            dto.Technology = project.Technology
            dto.ProjectType = project.ProjectType
            dto.UserId = project.UserId
            dto.StartDate = project.StartDate
            dto.EndDate = project.EndDate
            dto.Cost = project.Cost
    
            Return dto
    
        End Function
    
        Public Function GetProjectsByUserId(ByVal userid As String) As List(Of DtoProject) Implements IDaoProject.GetProjectsByUserId
    
            Dim dtos = New List(Of DtoProject)
    
            dtos = (From a In context.Projects.Where(Function(a) a.UserId.Contains(userid))
                    Select New DtoProject With {.ProjectId = a.ProjectId,
                                                .ClientName = a.ClientName,
                                                .ProjectName = a.ProjectName,
                                                .Technology = a.Technology,
                                                .ProjectType = a.ProjectType,
                                                .UserId = a.UserId,
                                                .StartDate = a.StartDate,
                                                .EndDate = a.EndDate,
                                                .Cost = a.Cost}).ToList()
           
    
            Return dtos
    
        End Function
    
        Public Sub CreateProject(ByVal dto As DtoProject) Implements IDaoProject.CreateProject
    
            Dim project = New Project() With {.ClientName = dto.ClientName,
                                                .ProjectName = dto.ProjectName,
                                                .Technology = dto.Technology,
                                                .ProjectType = dto.ProjectType,
                                                .UserId = dto.UserId,
                                                .StartDate = dto.StartDate,
                                                .EndDate = dto.EndDate,
                                                .Cost = dto.Cost}
            context.Projects.Add(project)
            context.SaveChanges()
    
           
        End Sub
    
        Public Sub UpdateProject(ByVal dto As DtoProject) Implements IDaoProject.UpdateProject
    
            Dim project = New Project()
           
            project = (context.Projects.Where(Function(a) a.ProjectId = dto.ProjectId)).SingleOrDefault()
           
            If Not IsNothing(project) Then
                project.ClientName = dto.ClientName
                project.ProjectName = dto.ProjectName
                project.Technology = dto.Technology
                project.ProjectType = dto.ProjectType
                project.UserId = dto.UserId
                project.StartDate = dto.StartDate
                project.EndDate = dto.EndDate
                project.Cost = dto.Cost
            End If
    
          
            If IsNothing(project) Then
                Exit Sub
            End If
    
            context.Entry(project).State = EntityState.Modified
            context.SaveChanges()
           
        End Sub
    
        Public Sub DeleteProject(ByVal id As Int32) Implements IDaoProject.DeleteProject
    
            Dim project As Project
            
            project = (context.Projects.Where(Function(a) a.ProjectId = id)).Include("Tasks").SingleOrDefault()
           
            If IsNothing(project) Then
                Exit Sub
            End If
    
            For i As Integer = 0 To  project.Tasks.Count - 1
                Dim task = project.Tasks(i)
                context.Entry(task).State = EntityState.Deleted
            Next
            
            context.Entry(project).State = EntityState.Deleted
            context.SaveChanges()
           
        End Sub
    
        End Class
    

    Entities......

    Public Class DtoProject
    
        Public Property ProjectId As Int32
        Public Property ClientName As String
        Public Property ProjectName As String
        Public Property Technology As String
        Public Property ProjectType As String
        Public Property UserId As String
        Public Property StartDate  As DateTime
        Public Property EndDate As DateTime? 
        Public Property Cost As Decimal
       
    End Class

    Sunday, December 30, 2018 2:09 AM
  • Hey
    Thx for your answer and your example.

    Also thanks for the links. 

    You described the basic of my question in a perfect way.

    There is still a big problem in your example, and your last explanation.

    In your example you are writing this particular function:

    Function GetProjectById(ByVal id As Int32) As DtoProject

    And here is the problem as I meant. Let's say, I would like to query by StartDate and EndDate

    Then I have two possibilities: 

    1) Return all DtoProjects and filter on client side
    2) Writing a special function 

    Both possibilities are not the way, I would like to do it. Because I limit myself.

    Loading all DtoProjects from database and filter on client side could mess in a big problem, when you have 100k+ DtoProjects. And writing special functions for each possible query would end in a messy code. 

    The idea would be use the Linq Expression tree. as function. But this is what I don't get. And this is the question at all.

    THX

    Sunday, December 30, 2018 6:59 AM
  • Both possibilities are not the way, I would like to do it. Because I limit myself.

    Loading all DtoProjects from database and filter on client side could mess in a big problem, when you have 100k+ DtoProjects. And writing special functions for each possible query would end in a messy code. 

    All I did is give you an example of how to do client/service architectural style and how to use two patterns the DAO and DTO pattern. The DTO pattern is shown on how to get the data back and forth between client and service. The DAO pattern is shown on how  to do low level database access.

    Now of course, you can use an express tree  with the ADO.NET Entity Framework an ORM.

    https://hassantariqblog.wordpress.com/2016/02/18/entity-framework-use-expression-trees-to-build-dynamic-queries-or-filter-using-expressionfunc/

    The key for you would be how to formulate the expression on the client-side and send it over to the service-side to the DAL using the DAO pattern against the ORM and query..

    Now,  the other course you could take is the repository pattern.

    https://martinfowler.com/eaaCatalog/repository.html

    <copied>

    A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to Repository for satisfaction. Objects can be added to and removed from the Repository, as they can from a simple collection of objects, and the mapping code encapsulated by the Repository will carry out the appropriate operations behind the scenes. Conceptually, a Repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer. Repository also supports the objective of achieving a clean separation and one-way dependency between the domain and data mapping layers

    <end>

    I am not a fan of the generic repository, becuase it's too generic. But there is nothing wrong in using a non generic repository.

    https://blog.sapiensworks.com/post/2012/03/05/The-Generic-Repository-Is-An-Anti-Pattern.aspx

    https://programmingwithmosh.com/entity-framework/common-mistakes-with-the-repository-pattern/

    https://blog.sapiensworks.com/post/2012/11/01/Repository-vs-DAO.aspx

    I'll give you a generic repository pattern, but keep in mind what is being discussed about the proper usage of the repository pattern.

    https://www.codeproject.com/Reference/731015/The-repository-pattern-in-VB-Net

    Now, it comes down to this. You're the one that has to architect and develop the solution. 

    All I have done is put the pieces before you so that you can see what is needed . It is more to it than what is solely happening with Linq. You are talking client/service architecture here, and I am showing you how to do it. It's up to you to figure out what you need to do using the code example and links given to you.

    I'll put it to you this way. Anyone can write some code, but the key is can you .architect the solution from the frontend to the backend, know what pieces need to be in place what technology, what patterns need to be used and do it effectively. 

    BTW, there is no such thing as an Exchange Layer. There is the Service Layer that sits between the client and the service. Maybe that's your intent. The Service Layer is being shown in the MVP link in one of the shows that was given to you. 

    https://martinfowler.com/eaaCatalog/serviceLayer.html

    Sunday, December 30, 2018 10:39 AM