SQL query, LINQ

Question

SQL query, LINQ

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I have the following query

SELECT tp.Nombre, 
       tp.IdTipoContacto
FROM TipoContacto tp 
LEFT JOIN Contacto c 
     on tp.IdTipoContacto = c.IdTipoContacto
WHERE NOT tp.Nombre = 'Compañia'

This brings me back

Facturacion 2
Facturacion 2

In my table TipoContacto I only have two records that are Billing and Company in my table Contacto I have records, several are Facturacion , and only one of compañia .

I want to make my query only return the remaining records of the ContactContact table when the id of compañia already exists in the other table, otherwise list the two Facturacion and Compañia , additionally in Linq

As you can see, the query brings me two equals, I would only need one in this case. Thank you

Something Additional To make me understand better:

select records, which meet the following conditions Tables: Contact, Contact Type, Client

1. if you have already made a contact with TypeContact Company, from a specified client Just list the others.

2. if there is no contact with type Company Contact, from a specific customer list all including company.

linq entity-framework linq-to-sql

asked by Kmiilo Berrio Montoya 08.09.2016 в 19:16

source

3 answers

Save id of an image clicked on asp.net (Web Forms) insert records by asynctask in a web service from android

score 1 · Answer 1

I would do it in two different consultations checking first if there is already a company.

var query;

If (Contacto.Exists( c => c.TipoContacto.Name == "Compañia")
{
 query = Contacto.Where( c => c.TipoContacto.Name == "Compañia");

}else
{
query = Contacto;
}

score 0 · Answer 2

Enclose your LINQ query in this way:

var result = (from tp in TipoContacto
    from c in Contacto
            .Where(c => c.IdTipoContacto == tp.IdTipoContacto)
            .DefaultIfEmpty()
    where tp.Nombre != "Compañia"
    select new 
    {
        Nombre = tp.Nombre,
        IdTipoContacto = tp.IdTipoContacto
    }).Distinct();

Response adapted from these answers at English .

score 0 · Answer 3

You can use Distinct.

var query = TipoContacto.Join(Contacto,
    c => c.IdTipoContacto,
    o => o.IdTipoContacto,
    (c, o) => new 
    {Nombre = c.Nombre, IdTipoContacto = o.IdTipoContacto})
        .Where(p=>p.Nombre != "Compañia").Distinct();

I guess the Customer structure includes either Name or Contact Type. if so, you just have to make a query like this:

var isUsuario =UsuarioL.Where(p => p.Nombre == "Compañia").Select(p => p.Nombre);

Use your result to decide if you have to use the where or not. or the distict if you also require it.

var isUsuario =UsuarioL.Where(p => p.Nombre == "Compañia").Select(p => p.Nombre);
    if(isUsuario.FirstOrDefault() == "Compañia"){   
        var query = LTipoContacto.Join(LContacto,
        c => c.IdTipoContacto,
        o => o.IdTipoContacto,
        (c, o) => new 
        {Nombre = c.Nombre, IdTipoContacto = o.IdTipoContacto})
        .Where(p=>p.Nombre != "Compañia").Distinct();
    }
    else{
        var query = LTipoContacto.Join(LContacto,
        c => c.IdTipoContacto,
        o => o.IdTipoContacto,
        (c, o) => new 
        {Nombre = c.Nombre, IdTipoContacto = o.IdTipoContacto})
        .Distinct();
    }

link