c# Web API Query SQL

Question

I have a Web API 2 project which is backed by a SQL DB. I have a table, UserAlerts, with fields Lat, Lon, Timestamp, UserID.

I have a UserAlert controller, and UserAlert model with fields matching value and type of the fields in the Db. What is the correct way to query the database and return a JSON object containing the rows which contain UserID=UserID from client?

I have tried

List<UserAlerts> userAlerts = await db.UserAlerts.Where(x => x.UserID == UserID)
            .Select(x => new UserAlerts
            {
                Lat = x.Lat,
                Lon = x.Lon,
                TimeTriggered = x.TimeTriggered,
                TimeEnded = x.TimeEnded,
                UserID = x.UserID
            }).ToListAsync();

But of course I get "The entity or complex type ',namespace>.Models.UserAlerts' cannot be constructed in a LINQ to Entities query.".

Any advice would be appreciated, I hope the question is clear!

David · Accepted Answer · 2015-11-10 13:40:49Z

1

If the types are the same (they're both called UserAlert after all...), then do you need the .Select() clause at all? Try:

List<UserAlerts> userAlerts = await db.UserAlerts
                                      .Where(x => x.UserID == UserID)
                                      .ToListAsync();

If the types are in fact different, then you can materialize the collection first before transforming it:

List<UserAlerts> userAlerts = await db.UserAlerts
                                      .Where(x => x.UserID == UserID)
                                      .ToListAsync()
                                      .Select(x => new UserAlerts
                                          {
                                              Lat = x.Lat,
                                              Lon = x.Lon,
                                              TimeTriggered = x.TimeTriggered,
                                              TimeEnded = x.TimeEnded,
                                              UserID = x.UserID
                                          })
                                      .ToListAsync();;

This would incur a performance hit if what you're materializing is considerably more data than what you're transforming it into, so be careful.

answered Nov 10, 2015 at 13:40

David

221k42 gold badges245 silver badges337 bronze badges

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

1 Comment

anthonyhumphreys Over a year ago

Bang on the money, optino one works like a charm, thank you! My old backend was php with mysql, these are murky waters for me ;)

Bellash · Accepted Answer · 2015-11-10 13:43:17Z

0

With

"The entity or complex type ',namespace>.Models.UserAlerts' cannot be constructed in a LINQ to Entities query."

This is because there is a table/class named 'UserAlerts' in your context. You should select anonymously like following

  List<UserAlerts> userAlerts = await db.UserAlerts.Where(x => x.UserID == UserID)
        .Select(x => new 
        {
            Lat = x.Lat,
            Lon = x.Lon,
            TimeTriggered = x.TimeTriggered,
            TimeEnded = x.TimeEnded,
            UserID = x.UserID
        }).ToListAsync();

answered Nov 10, 2015 at 13:43

Bellash

8,2776 gold badges57 silver badges95 bronze badges

Collectives™ on Stack Overflow

c# Web API Query SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related