CODEWITHSUNDEEP
c#sqlentity-frameworklinqlinq-query-syntax
ChathurawinD
ChathurawinD

Reputation: 784

Linq query to get the records of a table without using an object of the table

I'm using entity framework 6 to develop my c# application. I have named my data model as Allocation model and I have a table called JobTable.

My Database model class looks like this

 public partial class  Allocation : DbContext
    {
        public  Allocation()
            : base("name=Allocation")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
        public virtual DbSet<JOB_Header> JOB_Header { get; set; }
    }

and my job header looks like this

My Job header class looks like this Job jeader class is the class generated from entity frame work model for my table Job_header

public partial class JOB_Header
    {
        public int JobID { get; set; }
        public string CustomerCode { get; set; }
        public string CustomerName { get; set; }
        public string MobileNo { get; set; }
        public string LocationCode { get; set; }
        public System.DateTime JobDate { get; set; }
        public bool Status { get; set; }
        public string Remarks { get; set; }
    }

How can I query data for sql queries like following.?

SELECT TOP 1 * FROM JOB_Header ORDER BY JOBID DESC;

select CustomerName from JOB_Header where JobID =1;



using (DataControllers.AllocationJAEntities = new DataControllers.Allocation())
            {
                JHeaderModel = JAEntities.JOB_Header.Where(a => a.JobID == 1).FirstOrDefault();
            }

Usually I get data for an object like above. but i need to get a single field without reading data to an object of the class created for Table in data model getting all row details for the object. How to handle a normal query in this way ?

Upvotes: 0

Views: 1299

Answers (3)

Deepak Sharma
Deepak Sharma

Reputation: 4170

when we want to get just the name you can do by below changes. The concept is when you are finding my KEY so there will be NO or One record at max. then -

string name = string.Empty;
using (DataControllers.AllocationJAEntities = new DataControllers.Allocation())
{
    name = JAEntities.JOB_Header.Find(1)?.CustomerName;
}

notice I used the Find method, as we are searching using primary key, else we can use WHERE also.

REMEMBER if you use Find actually it will query your DB for full row like below SQL query -

select * from table where Id = 1

mean you DB full row will be return for your specific ID, to your code, and then you are reading just your name property.

But when you want to achieve something like below SQL Query -

SELECT CustomerName FROM table WHERE Key = 1

for that case answer by Rahul is correct. - 

string name = string.Empty;
using (DataControllers.AllocationJAEntities = new DataControllers.Allocation())
{
    name = JAEntities.JOB_Header
            .Where(a => a.JobID == 1)
            .Select(x => x.CustomerName)
            .FirstOrDefault();
}

To get the first record including order by you can (as Stephen mentioned above)- 

using (var context = new DataControllers.Allocation())
{
  var job = context.JOB_Header.OrderByDescending(j => j.JobID).FirstOrDefault();
}

Upvotes: 2

Stephen Witherden
Stephen Witherden

Reputation: 166

using (var context = new DataControllers.Allocation())
{
  var header = context.JOB_Header.OrderByDescending(j => j.JobID).FirstOrDefault();
}

Wasn't sure about your variable names so made my own

Upvotes: 4

Rahul
Rahul

Reputation: 77866

Why can't you select that field only then like below. Moreover, if your JobID is a key field (which it looks like) then I don't see the need for FirstOrDefault() at all since the Where() would up returning a single record only

JAEntities.JOB_Header.Where(a => a.JobID == 1).Select(x => x.CustomerName)

Upvotes: 2

Related Questions