none
Entity frame work POCO selectively read rows RRS feed

  • Question

  • Hi there,

    I am using Entities Framework 4.1 and Code first in my mvc application. Basically, I am reading a user table and populating a list of users. It works well, until I am going to return active users, whose active column is 1 in table.

    1. I can filter the result set at client side with the penalty of bring much more useless data. A bit of waste.

    2. I can use SqlQuery to pass the query, like:

            public IEnumerable<User> ActiveUsers

                {

                get

                    {

                    string sqlStr = "select * from v_user where active=1";

                    return Users.SqlQuery(sqlStr, new object[]{}).AsNoTracking();

                    }

                }

    But framework complains that there is no key defined. Because I am reading from a view, which has no primary key.

    3. I can modify the user class to have an ID field, like:

        [Table("V_User")]

        public class User

            {

            [Column("UserID")]

            public int ID { get; set; }

            public string DisplayName { get; set; }

            public int Active { get; set; }

            public string Email { get; set; }

            }

    But this time, framework throw an exception that "The data reader is incompatible with the specified 'Backend.Contexts.User'. A member of the type, 'ID', does not have a corresponding column in the data reader with the same name."

    So my questions are:

    1. Is it possible to selectively return rows using EF4.1 code first approach?

    2. In my case, how could make it return only the active users?

    Thanks in advance,

    Gary

     

    Wednesday, August 17, 2011 8:01 PM

Answers

  • Hi Gary,

    Welcome!

    According to your description, you just want to return the active users only, right?

    1.

    var activeUsers=Context.Users.Where(u=>u.active==1)
    

    The Linq will be translate to T-SQL: select * from User where active=1

    2.

    I test view without key and it works on my computer:

    return Users.SqlQuery("select * from v_user where active=1").AsNoTracking();
    

    3.About the related entities, you can refer this link

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 18, 2011 8:35 AM
    Moderator

All replies

  • Hi Gary,

    Welcome!

    According to your description, you just want to return the active users only, right?

    1.

    var activeUsers=Context.Users.Where(u=>u.active==1)
    

    The Linq will be translate to T-SQL: select * from User where active=1

    2.

    I test view without key and it works on my computer:

    return Users.SqlQuery("select * from v_user where active=1").AsNoTracking();
    

    3.About the related entities, you can refer this link

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 18, 2011 8:35 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 26, 2011 3:18 AM
    Moderator