Entity inner join in LINQ

2

I wanted to make an inner join so that I get a list with the names of Logins from the Integer List of UsuarioID of class LoginsAtivos .

public class Login
{
    public int ID { get; set; }
    [Required]
    public string Nome { get; set; }
    [Required]
    public string Senha {get;set; }
}

 public class LoginAtivo {
    public virtual int UsuarioID { get; set; }
    public DateTime inicio { get; set; }
}

I'm locked in the following function

context.LoginsAtivos.ToList();
    
asked by anonymous 28.06.2016 / 18:07

4 answers

0

Can solve my problem with the following code using lambda syntax. In it I quote the table I want to group LoginDao.getListUsuario() . I merge the table and then select the names of active users (ativo, login) => login.Nome

public static List<string> getListUsuario() {
            return contexto.LoginAtivo
                .Join(LoginDao.getListUsuario(),
                    ativo => ativo.UsuarioID,
                    login => login.ID,
                    (ativo, login) => login.Nome)
                .ToList();
        }
    
28.06.2016 / 23:52
3
  

I will respond with the premise that the relationship is 1: N between Login and LoginAtivo .

If you do a correct modeling, the Entity Framework does this work for you. For this, we will adjust some things in its structure.

public class Login
{
    [Key]
    public int ID { get; set; }
    [Required]
    public string Nome { get; set; }
    [Required]
    public string Senha {get;set; }

    public virtual ICollection<LoginAtivo> LoginsAtivos{get;set;}
}

 public class LoginAtivo {

    [Key, ForeignKey("Login")]
    public int UsuarioID { get; set; }
    public DateTime inicio { get; set; }

   public virtual Login Login{get;set;}
}

In this way, just make the normal query that is doing context.LoginsAtivos.ToList(); that every LoginAtivo will have its Login , and you can access it by the navigation property, example: loginAtivo.Login.Nome , where loginAtivo is an item of the list (return of your query in the bank).

    
28.06.2016 / 19:19
2

You can create an extension method to do this.

  

Call it like this.

context.LoginsAtivos
.Vw_LoginAtivoLogin(context);
  

Type a View.

public class vw_LoginAtivoLogin {
    public LoginAtivo loginAtivo { get; set; }
    public Login login { get; set; }
}
  

Method.

public static class LoginExt
{
    public static IQueryable<vw_LoginAtivoLogin> Vw_LoginAtivoLogin(
        this IQueryable<LoginAtivo> qrIn, WdbContext ctx)
    {
        return qrIn
            .Select(LA =>
                new vw_LoginAtivoLogin
                {
                    loginAtivo = LA,
                    login = ctx.Login.FirstOrDefault(L =>  L.ID == LA.UsuarioID),
                });
    }
}

If the ratio is 1: N, change the FirstOrDefault by Where and the view to

public class vw_LoginAtivoLogin {
    public LoginAtivo loginAtivo { get; set; }
    public List<Login> login { get; set; }
}
    
28.06.2016 / 18:54
-1
public class Login
{
    public int ID { get; set; }
    [Required]
    public string Nome { get; set; }
    [Required]
    public string Senha {get;set; }
}

 public class LoginAtivo {
    public virtual int UsuarioID { get; set; }
    public DateTime inicio { get; set; }
}


var nomes = from login in contexto.listloginativo()( contexto LoginAtivo)   join
            user in  contexto.listlogin() (contexto do login) on login.UsuarioId equals user.ID 
            select user.Nome;

Or something like this

    
28.06.2016 / 19:29