how to get two table combine record using entity framework with linq c#?

Question

I want to get two table combine data using linq c# lambda expression and get fix number of column get in the list. I have try this code but I don't know how to write query in linq lambda expression.

this is my method:

public ActionResult GetUserList(string searchRequest)
{          
    List<User> userList = new List<User>();              
    if (searchRequest != null)
    {
        if (searchRequest == "All")
        {
// here i want to write select query but how i don't know
            userList = db.user.ToList();
        }
        else if (searchRequest == "Flight") 
        {
            userList = db.user
                .Where(t => t.type_id == (int)ServiceTypeEnum.Flight)
                .ToList();
        }                   
    }                                       
    return Json(new { data = userList });
}

any one have the idea about this query then please let me know how can do.

It would be helpfull if you could be more specific. Example data would be good and how your result should look like. — Mighty Badaboom
– Mighty Badaboom, Commented Mar 10, 2017 at 12:09
it is already getting translated in to select query, do you want to get specific columns instead? — Ehsan Sajjad
– Ehsan Sajjad, Commented Mar 10, 2017 at 12:09
@MightyBadaboom look like something want in user table get only fname,lname,etc.. — coderwill
– coderwill, Commented Mar 10, 2017 at 12:11

Chris Schaller · Accepted Answer · 2017-03-10 13:15:57Z

The issue that you will experience with lambdas where you select specific fields is that the result is normally an anonymous type. Anonymous types from two different queries cannot easily be joined together in a list because the compiler cannot verify the structure or equality of the types.

There are other ways around this...

The best practice approach is to create a formal type definition and use that so that you can manipulate your objects outside of your lambda expressions. Note here that I have assumed a simple example structure that is a sub-set of user:

public ActionResult GetUserList(string searchRequest)
{
    try
    {
        List<UserSearchResult> UserList = new List<UserSearchResult>();
        if (searchRequest != null)
        {
            if (searchRequest == "All")
            {
                UserList.AddRange(db.user.Select(u => new UserSearchResult { Title = u.Title, FirstName = u.Firstname, LastName = u.Lastname })); // here i want to write select query but how i don't know
            }
            else if (searchRequest == "Flight")
            {
                UserList.AddRange(db.user.Where(t => t.type_id == (int)ServiceTypeEnum.Flight)
                    .Select(u => new UserSearchResult { Title = u.Title, FirstName = u.Firstname, LastName = u.Lastname }));
            }
        }
        return Json(new { data = UserList });
    }
    catch (Exception ex)
    {
        throw;
    }
    return Json(null);
}

public class UserSearchResult
{
    public string Title { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Because we have explicitly cast the result of our selection of specific fields to a formal type, we can now use that type in operations outside of your queries and can even manipulate the values.

Mohammad Akbari · Accepted Answer · 2017-03-10 13:34:23Z

Define UserDto class for columns that you want select

public class UserDto
{
    public int Id{get;set;}

    public int Name{get;set;}

    //Other Properties
}

Then change your code to following

public ActionResult GetUserList(string searchRequest)
{
    try
    {                            
        if (searchRequest != null)
        {
            IQueryable<User> query; 

            if (searchRequest == "All")
            {
                query = db.user.AsQueryable(); // here i want to write select query but how i don't know
            }
            else if (searchRequest == "Flight") 
            {
                UserList = db.user.Where(t => t.type_id == (int)ServiceTypeEnum.Flight);
            }  

            if(query != null)
            {
                var list = query.Select(e=> new UserDto
                {
                    Id = e.Id,
                    Name = e.Name
                    //Other properties
                }).ToList();

                return Json(new { data = list });
            }   
        }                                       

   }
   catch (Exception ex)
   {
       throw;
   }
   return Json(null);
}

Amy B · Accepted Answer · 2017-03-10 14:05:19Z

0

I think the local variable is holding you back. Just return the result you want.

public ActionResult GetUserList(string searchRequest)
{
    if (searchRequest == "All")
    {
       var users = db.user
           .Select(user => new {user.Name, user.Address.ZipCode})
           .ToList();

       return ToJson(users);
    }
    else if (searchRequest == "Flight") 
    {
        List<User> users = db.user
            .Where(t => t.type_id == (int)ServiceTypeEnum.Flight)
            .ToList();

        return ToJson(users);
    }

    return ToJson(new List<User>());
}

private ActionResult ToJson<T>(T list)
{
    return Json(new { data = list });
}

answered Mar 10, 2017 at 14:05

Amy B

110k21 gold badges140 silver badges191 bronze badges

Collectives™ on Stack Overflow

how to get two table combine record using entity framework with linq c#?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related