C# Linq-to-SQL multiple columns

Question

I am currently trying to select multiple columns in C# with Linq-to-SQL. When looking at other threads just as this one it is a pretty simple thing to do, so I tried:

var users = context.GetTable<DbHelper.User>();

var query = from u in users
            where u.Email == TbUsername.Text
            select new { u.Active, u.Password };

if(!query.Any())
{
  MessageBox.Show("Could not find an account with that Email");
  return;
}

var result = query.First();   // Error occurs here

User table in DbHelper:

[Table(Name="Users")]
public class User 
{
    [Column(IsPrimaryKey = true, AutoSync = AutoSync.OnInsert, IsDbGenerated = true)]
    public int Id { get; set; }

    [Column]
    public int RoleID { get; set; }

    [Column]
    public string Email { get; set; }

    [Column]
    public string Password { get; set; }

    [Column]
    public string Firstname { get; set; }

    [Column]
    public string Lastname { get; set; }

    [Column]
    public int OfficeID { get; set; }

    [Column]
    public string Birthdate { get; set; }

    [Column]
    public int Active { get; set; }
}

The table looks like this in SQL Server:

[ID]        INT           IDENTITY (1, 1) NOT NULL,
[RoleID]    INT           NOT NULL,
[Email]     NVARCHAR(150) NOT NULL,
[Password]  NVARCHAR(50)  NOT NULL,
[FirstName] NVARCHAR(50)  NULL,
[LastName]  NVARCHAR(50)  NOT NULL,
[OfficeID]  INT           NULL,
[Birthdate] DATE          NULL,
[Active]    BIT           NULL,

Which results in an error

System.InvalidCastException: The conversion is invalid

What is wrong with my code? It's really confusing since it seems to work for others. If you need more code please let me know

Do your class data types map to the correct corresponding sql types? — William Xifaras
– William Xifaras, Commented May 19, 2019 at 15:03
Yes they do except for Birthdate, because that is Date which does not exist in C# I believe. I can add the Database structure to the thread if that helps..? — Benjamin Frost
– Benjamin Frost, Commented May 19, 2019 at 15:05
If I understand you correctly it returned int: Active and string: Password BUT as Anonymours Types — Benjamin Frost
– Benjamin Frost, Commented May 19, 2019 at 15:09
OT both .Any() and .First() will cause a database query. You may want a .ToList() first and inspect the results from that — Hans Keﬆing
– Hans Keﬆing, Commented May 19, 2019 at 15:11

Hans Keﬆing · Accepted Answer · 2019-05-19 15:44:50Z

1

As the type of the Active column in your database table is 'bit', EF expects a property of type 'bool'. 0==false, 1==true, as you might expect.

answered May 19, 2019 at 15:44

Hans Keﬆing

39.5k10 gold badges84 silver badges119 bronze badges

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

1 Comment

Brian White Over a year ago

This is it. Your data class doesn't match the underlying values.

marc_s · Accepted Answer · 2019-05-19 14:57:04Z

1

Try to use .FirstOrDefault() :

var result = query.FirstOrDefault();

If the result is null after this call, then your selection just didn't return any rows from the database table.

answered May 19, 2019 at 14:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

4 Comments

Benjamin Frost Over a year ago

Still the same error: Invalid Cast Exception. Iam also checking if the query finds any result, I should probably add it to the thread

Avi Meltser Over a year ago

First() throws ArgumentNullException in that case, not an InvalidCastException

DS Passionate Over a year ago

@BenjaminFrost rather than checking ditectly right var result = query. FirstOrDefault() ; Then go for checking is it null or not.

Benjamin Frost Over a year ago

@DSPassionate check the updated Thread Iam checking using the query.Any() function

Collectives™ on Stack Overflow

C# Linq-to-SQL multiple columns

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related