Entity Inner Join in LINQ

Asked

Viewed 582 times

2

I wanted to make an Inner Join so that I get a list of the names of Logins from the Integer List of UsuarioID 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 am locked in the following function

context.LoginsAtivos.ToList();
c# entity-framework linq
  • 2

    What have you tried?

  • How is the list of integers ? And why Inner Join?

  • @jbueno I completed my question @Marconcíliosouza the entire list comes from int Usuarioid. and I wanted to do an Inner Join because my starting point is the LoginsAtivos and I want to have the name list of Login

  • I don’t think I understand your doubt. You have a list of LoginAtivo and wants to know what the Login it belongs? If you normalize and add the relationships in LoginAtivo and Login, you can get the values you want only with context.LoginsAtivos.ToList();.

  • @Randrade this msm. I wanted to know how.

4 answers

3

I will answer with the premise that the relationship is 1:N amid Login and LoginAtivo.

If you do a correct modeling, the Entity Framework does all this work for you. For that, let’s adjust some things in your 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;}
}

That way, just do the normal consultation you’re doing context.LoginsAtivos.ToList(); that each LoginAtivo will have his Login, and you can access by the navigation property, example: loginAtivo.Login.Nome, where loginAtivo is a list item (return of your query in the bank).

  • 1 to N or 1 to 1?

  • @Romaniomorrisonmendez In the context of my reply, a Login can have multiple Loginsativos, so 1 to 1 would not be right. But the AP didn’t explain it properly, so...

  • Your answer is perfect for 1:0-1. It would be better to put some considerations on it. In my view, it would be the best approach.

  • @Ciganomorrisonmendez In this case, a login may have 0 or N Loginsativos, so 1:1 does not fit this view. Now, a Loginativo can have 1 Login, so yes, 1:1. But I will edit the answer and add some more details.

2

You can create an extension method to do this.

Call it that.

context.LoginsAtivos
.Vw_LoginAtivoLogin(context);

Like 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 for Where and the view to 

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

0


Manage to solve my problem with the following code using the syntax lambda. In it I quote the table I want to group LoginDao.getListUsuario(). I join the table and then select active user names (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();
        }

-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 that

Browser other questions tagged c# entity-framework linq

You are not signed in. Login or sign up in order to post.