Fetch single table data from Entity Framework

Question

I have five table in Sql-Server, and which have linked using foreign keys.

Below i am displaying the image of database tables and links.

For example i tried to fetch user data of a particular user ID from user table, i am getting all rows with inner join to all five tables.

But i just need data from only User table. I tried to write query , sql procedure and also Linq.

In all i am getting the data which is joined with all five tables.

How can i achieve the data from only one table ?

My Try using Procedure ,

       object[] xparams = {
        new SqlParameter("@UserName", "Admin"),
        new SqlParameter("@UserPwd", "1234"),
        new SqlParameter("@AppType", 1)

        };
    var user = db.Database.ExecuteSqlCommand("exec proc_authenticate_tocken @UserName, @UserPwd, @AppType ", xparams);

Another using Query ,

db.Users.SqlQuery("select * from users where PhoneNumber ='"+ user_name + "' and   Password = '" + password + "' and TypeId=" + user_type + " and Status = 1;").ToList();

Lsing Linq,

var ty = db.Users.Where(emp => emp.Password == password && 
        emp.PhoneNumber == phone_number)
                           ;

Code below is Entity Model Class, auto generated using Entity Framework.

 public partial class User
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public User()
    {
        this.TockenPools = new HashSet<TockenPool>();
        this.TockenProcesses = new HashSet<TockenProcess>();
    }

    public int Id { get; set; }
    public Nullable<int> TypeId { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    public string PhoneNumber { get; set; }
    public string ActualName { get; set; }
    public Nullable<System.DateTime> LastLogin { get; set; }
    public Nullable<int> LoginCount { get; set; }
    public Nullable<int> Status { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<TockenPool> TockenPools { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<TockenProcess> TockenProcesses { get; set; }
    public virtual User Users1 { get; set; }
    public virtual User User1 { get; set; }
    public virtual UserType UserType { get; set; }
}

I need data from other tables, but in this context i just need data from USER table, later i need data with Joins from other tables too. — jidh, Jul 27 '17 at 09:34
check my answer. I wrote it according to my understanding about your Q. If anything different notify me. — SilentCoder, Jul 27 '17 at 09:39
I am trying to make lazyLoading disable , But still i am getting same error. — jidh, Jul 27 '17 at 09:40
Well, please show how you disabled LL and what "error" you get. If you only query `db.Users`, without LL, there's no way you can get related data. *Unless* you loaded the data into the same context before querying users. — Gert Arnold, Jul 27 '17 at 10:13
Its Not in code, in property i disabled it, but data from other tables are removed, but table names are showing in result. Earlier I disabled in code in derived class of dBContext, this.Configuration.LazyLoadingEnabled = false; — jidh, Jul 27 '17 at 10:23
*table names are showing in result* What do you mean? If you query `db.Users.Where(emp => emp.Password == password && emp.PhoneNumber == phone_number)` the query can't possibly contain any joins. — Gert Arnold, Jul 27 '17 at 10:39

score 0 · Answer 1 · answered Jul 27 '17 at 09:37

0

Hi According to my understand about your question, you need only the data from users table. So please try below code,

var SelectedUser = db.Users.Where(emp => emp.Password == password && 
    emp.PhoneNumber == phone_number).select(usr => new User{UserName =  usr.UserName , Password = usr.Password }).FisrtOrDefault();

like wise you can include anything related to your user class

answered Jul 27 '17 at 09:37

SilentCoder

1,970
1
16
21

Thank you for your effort for me, but I am getting following error on using your syntax, ----- Message "The entity or complex type 'XXXXXXXXXXXXeModel.User' cannot be constructed in a LINQ to Entities query." string – jidh Jul 27 '17 at 09:47
You can create a duplicate `class` which have the properties you nee from `users` class and use that – SilentCoder Jul 27 '17 at 09:52
https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query this link will help you to understand what I'm trying to tell about the exception – SilentCoder Jul 27 '17 at 09:54
can you please see my answer above , and please correct me if my approach is wrong . – jidh Jul 27 '17 at 09:55
It is better to have a 'class' bind to it, then it is easy when handling you result. I have done the same approach with that User class.. – SilentCoder Jul 27 '17 at 10:03
Is Entity Framework will give always result joining all tables , what ever is linked using Foregin_Key , though we query for only single table ? Is that so ?? – jidh Jul 27 '17 at 10:12

score 0 · Answer 2 · answered Jul 27 '17 at 09:53

In My Context, i managed to get the result, using LINQ itself,

But i am not so sure that this is a good practice or not, since the

ObjUser object carries all data by joining all the tables in the digram.

        var Result = from ObjUser in db.Users
                           where ((ObjUser.PhoneNumber == user_name ||
                           ObjUser.UserName == user_name) &&
        (ObjUser.Password == password))
                           select new
                           {
                               ObjUser.Id,
                               ObjUser.ActualName,

                               ObjUser.LastLogin,
                               ObjUser.PhoneNumber
                           };
        var ty = db.Users.Where(emp => emp.Password == password &&
        emp.PhoneNumber == user_name)
                           ;

*carries all data by joining all the tables in the diagram* Well, as I said above, that's impossible. Convince me by showing the SQL query that is generated by this single LINQ statement. — Gert Arnold, Jul 28 '17 at 07:38

Fetch single table data from Entity Framework

2 Answers2