How to make a Join in C#

Asked

Viewed 164 times

1

Guys I’m having a question, I can make a SQL command (Ex.: select * from T_TABELA) in my C# repository like I do in the database, but I’d like to know if you can do a command join in the same C# I do in the bank?

I was trying this command, but always from error, what I’m doing wrong?

select * from tblModelo modelo join tblMarca marca on marca.IdMarca = modelo.IdModelo;

In my SQL SERVER database this command works!

Code in C#:

 public List<clsModelo> listar()
        {
            strQuery = "select * from tblModelo join tblMarca on IdMarca = IdModelo";
            List<clsModelo> result = db.Database.SqlQuery<clsModelo>(strQuery).ToList();
            return result;
        }

Marca Class:

public class clsMarca

    {
       [Key]
       public int IdMarca { get; set; }

       [Required(ErrorMessage = "Informe o nome da marca")]
       [MinLength(3), MaxLength(50)]
       public string Marca { get; set; }

       public virtual ICollection<clsModelo> Modelos { get; set; }
    }

Model Class:

public class clsModelo
    {
        [Key]
        public int IdModelo { get; set; }

        [Required(ErrorMessage="Informe o nome do Modelo")]
        [StringLength(40, MinimumLength = 3, ErrorMessage = "O nome do modelo precisa ter no mínimo 3 letras")]
        public string Modelo { get; set; }

        [Display(Name = "Marca")]
        public int IdMarca { get; set; }

        [ForeignKey("IdMarca")]
        public virtual clsMarca Marca { get; set; }

        List<clsModelo> lstModelo { get; set; }
    }

An error that appeared said that Idmarca does not belong to the context of Model, and I informed that it is a foreign key.

c# entity-framework

  • What’s the mistake?.

  • How are you trying to do in the c#?

  • Is this excerpt from SQL or C#? Do you want to join in Linq?

  • puts the code because in c# there are n ways to fetch data in a DB.

  • This Join command is the same way I am doing in c# and in the database, what are the ways to do a single search in two tables?

  • Show me your code!

  • strQuery = "select * from tblModelo join tblMarca on marIdMarca = modIdModelo"; this SQL can give problems have to put the names or alias because of the problem! Although you did not report the error that is returning ...

  • Sorry, I edited the question, see if you can understand now.

  • There is an answer using lambda here: https://answall.com/questions/17618/join-com-tres-ou-mais-tabelas-com-lambda

  • 1

    It wouldn’t just be like that then: strQuery = "select * from tblModelo join tblMarca on IdMarca = IdModelo";

  • I was adapting to put in question and forgot to change

  • Didn’t work out? What was the exception thrown?

  • 1

    Take a look at this link (Obs: stackoverflow in English): https://stackoverflow.com/questions/9005095/establish-foreign-key-connection-using-entity-framework-with-sql-queries

Show 8 more comments

1 answer

2


Possibly the return will not be of type "clsModel", because it will contain the fields of tblModel + tblMarch.

Try running sql without using this cast for clsModel type. Something like this:

var result = db.Database.SqlQuery(strQuery).ToList();
  • 1

    Sqlquery is incorrect. Right would be Sqlquery<TYPE> where "TYPE" can be an object or int or string etc...

Browser other questions tagged c# entity-framework

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