Home / Questions / 413880
Accepted
C Aldair
Asked: 2021-12-12 12:13:13 +0800 CST 2021-12-12 12:13:13 +0800 CST

Convert Procedure to LinQ

  • 772

How can I convert this procedure in linQ, I am learning and I would like an example of how to convert it and be able to guide me so I can start practicing on my own, I was looking for examples but they only give the basics, this procedure handles joins and has 2 inputs, I want to pass it to ASP.NET Core

this is the procedure

USE [SVMQAS]
GO
/****** Object:  StoredProcedure [dbo].[ugv_sp_pin_validation]    Script Date: 10/12/2020 11:57:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[ugv_sp_pin_validation] 
@IMEI varchar(25),
@PIN varchar(15)

as
BEGIN
  -- date: 31/07/2020
  declare @cod_persona varchar(110)
  declare @es_reparto bit = 0

    select @cod_persona = p.cod_persona
    from afcma_persona p
      join afcma_vendedor v on v.cod_vendedor = p.cod_persona
    where p.IMEI = @IMEI and p.cod_clave=@PIN

  if @cod_persona is null
    begin
      select @cod_persona = cod_persona, @es_reparto = 1
      from afcma_persona
      where imei2 = @IMEI and cod_clave=@PIN
    end

    select p.cod_persona,
           p.cod_tipo_persona,
           iif(@es_reparto = 0, p.dsc_razon_social, concat('REPARTO DE ', p.dsc_razon_social)) dsc_razon_social,
           p.dsc_nombre_comercial,
           p.num_ruc,
           p.num_doc_identidad,
           p.flg_afecto_percepcion,
           p.cod_clave,
           p.IMEI,
           p.PERNR,
           p.fch_ultima_venta,
           p.imp_ultima_venta,
           iif(@es_reparto = 1, cast(1 as bit), v.iti_reparto) iti_reparto,
           iif(@es_reparto = 1, cast(0 as bit), v.iti_preventista) iti_preventista,
           p.cod_repartidor,
           p.imei2
    from afcma_persona p
      join afcma_vendedor v on v.cod_vendedor = p.cod_persona
    where p.cod_persona = @cod_persona

END
sql-server

1 Answers

  1. Best Answer

    That procedure has 3 select queries, you should do the same steps.

    To break down the query and understand, it goes like this:

    1: from persona in "tabla BD-> "person is your internal query variable"

    2: join vendedor ->"segunda variable interna de otra tabla" in "tabla BD" on vendedor.cod_vendedor equals persona.cod_persona(in this line you have to call the tables in this order because you will notice that if you do it the other way around, the intellisense will not tell you the internal variables.

    3: You will notice that I wrapped the query between ( ) and I execute a method, so that it brings a single record. If there are many you could do ().ToList(). Otherwise you remove the parentheses and then execute the query in aforeach

     private Persona ObtenerPersona(int _Imei)
 {
  var persona = (from person in db.afcma_persona
                join vendedor in afcma_vendedor on p.cod_vendedor equals person.cod_person
                where person.Imei == _Imei
                select person).FirstOrDefault();
     if(persona != null)
     {
       //rellenas tu objeto persona
       return new Persona()
                  {
                   cod_persona = persona.cod_Persona;
                  }
     }
     return persona;
  }

    Second option to execute the same query.

      var persona = from person in db.afcma_persona
                join vendedor in afcma_vendedor on p.cod_vendedor equals person.cod_person
                where person.Imei == "TuVariable"
                select person;


 foreach(var item in persona)
 {

 }
    • 0