LINQ SqlQuery<T> with random number of fields selected

Question

I am making a run time sql query and getting data from database using LINQ SqlQuery<>

SchoolSoulLibrary.SchoolSoulDataEntities ss = new SchoolSoulLibrary.SchoolSoulDataEntities();
string query1;
    var li = ss.Database.SqlQuery<MasterBank>(query1).ToList();

where MasterBank class is

public Partial class MasterBank
{
        public MasterBank()
        {

        }
        public decimal BankId { get; set; }
        public string BankName { get; set; }
        public Nullable<decimal> UserId { get; set; }
        public Nullable<decimal> SchoolId { get; set; }
}

if i am executing this query

query1 = "Select * from MasterBank"; or
query1 = "Select BankId,BankName,UserId ,SchoolId  from MasterBank";

Its not giving any error and returning all data

but if i am executing this query

query1 = "Select BankName,SchoolId  from MasterBank";

error occured
i understand the reason of this error that it return a result of the type of class MasterBank but what can i do now because query1 is generating runtime with random no of properties of class MasterBank.
is there any other alternative of doing it rather than SqlQuery<>
kindly suggest

BankId is not nullable, so if the query doesn't fetch BankId then you get the error!? Can you make BankId nullable? — erikH
– erikH, Commented Nov 13, 2013 at 11:52
@erikH, it looks like a bank identifier so I doubt it could be nullable. If this is the case, I worry that it is a decimal and not an Int32 or Int64 (or even a Guid). — Moo-Juice
– Moo-Juice, Commented Nov 13, 2013 at 12:01
@Moo-Juice, Since you don't always fetch the bank id it is not required. Could it be a way to create a TempMasterBank class that has BankId as nullable, along with the other properties. Then the query would always work and you can then convert to the MasterBank class. It is up to your convert code to decide how to create an MasterBank instance if BankId is not known. — erikH
– erikH, Commented Nov 14, 2013 at 9:12
@Moo-Juice, Or you could adjust the query to always fetch BankId!? — erikH
– erikH, Commented Nov 14, 2013 at 9:13

Klark · Accepted Answer · 2013-11-13 11:55:55Z

2

All the fields that are optional need to be marked as optional (nullable) in MasterBank class.

This one shouldn't cause any problems:

query1 = "Select BankId ,SchoolId  from MasterBank";

So the quickest fix is just to make all your fields nullable.

answered Nov 13, 2013 at 11:55

Klark

8,3283 gold badges40 silver badges63 bronze badges

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

Comments

Ben · Accepted Answer · 2013-11-13 12:02:10Z

0

EDIT

My original answer was way off...

I think you will need the type you use to match the result you expect. So define a new type to match your query:

class SomeClass
{
    public string BankName {get;set;}
    public int SchoolId {get;set;}
}

SchoolSoulLibrary.SchoolSoulDataEntities ss = new SchoolSoulLibrary.SchoolSoulDataEntities();
string query1;
    var li = ss.Database.SqlQuery<SomeClass>(query1).ToList();

If you don't know until runtime what fields you will return - consider using a dataset instead of entity framework or try something like this:

http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-4-1-SqlQuery

edited Nov 13, 2013 at 12:02

answered Nov 13, 2013 at 11:48

Ben

1,96316 silver badges17 bronze badges

Collectives™ on Stack Overflow

LINQ SqlQuery<T> with random number of fields selected

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related