Adding a list inside a list from a linq to sql query

Question

Hello I am trying to add carriers to my friendmodel but i am unsure how to do so.

Here is my model:

public class FriendModel
    {
        public int? Id { get; set; }

        [Required]
        [StringLength(50)]
        [Display(Name = "First Name")]
        public string FirstName { get; set; }

        [Required]
        [StringLength(50)]
        [Display(Name = "Last Name")]
        public string LastName { get; set; }

        [Required]
        [StringLength(10)]
        [Display(Name = "Phone Name")]
        public string PhoneNumber { get; set; }

        public List<CarriersModel> Carriers { get; set; }  

        public int UserId { get; set; }
        public int? SelectedCarrier { get; set; }
    }

Here is my code:

var query = from f in db.Friends
                join uf in db.Users_Friends on f.Id equals uf.FriendId
                join c in db.Carriers on f.CarrierId equals c.Id
                select new {Friends = f, Users_Friends = uf, Carrier = c};

            var friends = new List<FriendModel>();

            foreach (var row in query)
            {
                friends.AddRange(query.Select(friend => new FriendModel()
                {
                    Id = friend.Friends.Id,
                    FirstName = friend.Friends.FirstName,
                    LastName = friend.Friends.LastName,
                    PhoneNumber = friend.Friends.PhoneNumber,
                    UserId = friend.Friends.Id,
                    Carriers = new List<CarriersModel>()
                    {
                        CarrierName = friend.Carrier.CarrierName,
                        CarrierEmail = friend.Carrier.CarrierEmail,
                    }
                }
                ));
            }

When I try to add the carriers like I do in the above code it errors. (Cannot resolve symbol). Is this not the way to do this?

How many db.Carriers are there supposed to be per db.Friends? Your model suggests there could be multiple carriers, but your query is only selecting one — berkeleybross
– berkeleybross, Commented Jan 2, 2014 at 1:33

MichaC · Accepted Answer · 2014-01-02 01:21:41Z

1

Instead of

                Carriers = new List<CarriersModel>()
                {
                    CarrierName = friend.Carrier.CarrierName,
                    CarrierEmail = friend.Carrier.CarrierEmail,
                }

Try

                Carriers = new List<CarriersModel>()
                {
                  new CarriersModel(){
                    CarrierName = friend.Carrier.CarrierName,
                    CarrierEmail = friend.Carrier.CarrierEmail,
                  },
                }

update: Maybe change your foreach loop to make some more sense

        foreach (var row in query)
        {
            friends.Add(new FriendModel()
            {
                Id = row.Friends.Id,
                FirstName = row.Friends.FirstName,
                LastName = row.Friends.LastName,
                PhoneNumber = row.Friends.PhoneNumber,
                UserId = row.Friends.Id,
                Carriers = new List<CarriersModel>()
                {
                    new CarriersModel(){
                        CarrierName = row.Carrier.CarrierName,
                        CarrierEmail = row.Carrier.CarrierEmail,
                    }
                }
            });
        }

edited Jan 2, 2014 at 1:21

answered Jan 2, 2014 at 1:04

MichaC

13.7k2 gold badges49 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

allencoded Over a year ago

Tried that it doesn't show any errors but throws an exception Cannot create a query result of type 'System.Collections.Generic.List`1[PandaBrew.Models.CarriersModel]'.

MichaC Over a year ago

ah btw why are you not using the row from your iterator? . This doesn't make sense at all to me

berkeleybross Over a year ago

+1 The updated answer should work because the foreach is forcing the enumeration and stopping it from being a SQL Query

berkeleybross · Accepted Answer · 2014-01-02 01:13:45Z

0

I dont think you can make a list. Try having an IEnumerable instead, and use this:

... snip ...
UserId = friend.Friends.Id,
Carriers = friend.Carrier.Select(c => new CarriersModel
    {
        CarrierName = c.CarrierName,
        CarrierEmail = c.CarrierEmail,
    })
etc

and in your model, you should have

public IEnumerable<CarriersModel> Carriers { get; set; }

As an aside, you don't need to do the foreach (var row in query) - the select will iterate all rows. You could have:

var friends = query.Select(<insert select here>)
                   .ToList();

answered Jan 2, 2014 at 1:13

berkeleybross

1,3862 gold badges14 silver badges31 bronze badges

2 Comments

allencoded Over a year ago

adding that second select gives me cannot resolve symbol select. the select is part is invalid.

berkeleybross Over a year ago

What type is friend.Carrier?

field_b · Accepted Answer · 2014-01-02 01:33:21Z

0

I am not familier with Linq to SQL, but this should work:

      List<FriendModel> friendModels = (from f in db.Friends
                join uf in db.Users_Friends on f.Id equals uf.FriendId
                join c in db.Carriers on f.CarrierId equals c.Id
                select new FriendModel()
                {
                    Id = f.Id
                    FirstName = f.FirstName,
                    LastName = f.LastName,
                    PhoneNumber = f.PhoneNumber,
                    UserId = f.Id,
                    Carriers = c.Select(i=> new CarriersModel() { CarrierName = i.Name,  CarrierEmail = i.CarrierEmail })
                 }).ToList();

edited Jan 2, 2014 at 1:33

answered Jan 2, 2014 at 1:18

field_b

6986 silver badges21 bronze badges

4 Comments

berkeleybross Over a year ago

ToList() wont work in a query, for the same reason he can't create a new List inside the query

field_b Over a year ago

His collection initialization is not correct. He is initializing a collection, and then setting properties for collection objects without instantiating them. He is not creating a valid list.

berkeleybross Over a year ago

You are correct in that he has not got collection initialization syntax correct, however your code (as far as I know) will not work because you cannot call ToList() inside of a query. (You can do what you have suggested in LinqToObjects, because that isn't a query)

field_b Over a year ago

@berkeleybross: Thanks for the clarification. I guess it would be best to deal with the IEnumerable<T> then.

Collectives™ on Stack Overflow

Adding a list inside a list from a linq to sql query

3 Answers 3

3 Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related