Entity Framework Dynamic LINQ and joining the same table twice - c#

I need a way for creating dynamic linq queries with dynamic select columns in Entity Framework. I searched a lot and found some good reference on the net.
I think the best and easiest way is using Dynamic LINQ. But there is a problem with this way. According to this link, there is a bug in join with navigation properties in entity framework that in some cases it joins the same table twice. Fortunately there is a solution for that and we can use this solution to solve that problem. But I don't know how to use this solution in Dynamic LINQ way. Can anyone tell me how to avoid this issue in Dynamic LINQ?
This is my code:
School Context:
public class SchoolContext : DbContext
{
public SchoolContext() : base("SchoolDbContext")
{
Configuration.LazyLoadingEnabled = false;
}
public DbSet<Student> Students { get; set; }
public DbSet<Grade> Grades { get; set; }
public DbSet<GradGroup> GradGroups { get; set; }
}
Student class:
public class Student
{
public int StudentID { get; set; }
public string StudentName { get; set; }
public DateTime? DateOfBirth { get; set; }
public byte[] Photo { get; set; }
public decimal Height { get; set; }
public float Weight { get; set; }
[ForeignKey("Grade")]
public int? GradeId { get; set; }
public Grade Grade { get; set; }
}
Grade class:
public class Grade
{
public int GradeId { get; set; }
public string GradeName { get; set; }
public string Section { get; set; }
public int? GroupId { get; set; }
public GradGroup Group { get; set; }
public ICollection<Student> Students { get; set; }
}
GradGroup class:
public class GradGroup
{
public int Id { get; set; }
public string GroupName { get; set; }
}
My query:
string[] selectiveProperties = new string[] { "StudentName", "Grade.GradeName", "Grade.Group.GroupName" };
using (var db = new SchoolContext())
{
var students = db.Students.Where(s => s.Grade.GradeName ==
"Grade_1" && s.Grade.Group.GroupName == "Group_1");
var query = students.Select(typeof(StudentDTO), "new(" +
string.Join(", ", selectiveProperties.ToArray()) +
")")
.ToDynamicList<StudentDTO>();
return query;
}
And this is generated SQL:
SELECT
[Filter1].[GradeId1] AS [GradeId],
[Filter1].[StudentName] AS [StudentName],
[Extent4].[GradeName] AS [GradeName],
[Extent5].[GroupName] AS [GroupName]
FROM (SELECT [Extent1].[StudentName] AS [StudentName], [Extent1].[GradeId] AS [GradeId2], [Extent2].[GradeId] AS [GradeId1]
FROM [dbo].[Students] AS [Extent1]
INNER JOIN [dbo].[Grades] AS [Extent2] ON [Extent1].[GradeId] = [Extent2].[GradeId]
INNER JOIN [dbo].[GradGroups] AS [Extent3] ON [Extent2].[GroupId] = [Extent3].[Id]
WHERE (N'Grade_1' = [Extent2].[GradeName]) AND (N'Group_1' = [Extent3].[GroupName]) ) AS [Filter1]
LEFT OUTER JOIN [dbo].[Grades] AS [Extent4] ON [Filter1].[GradeId2] = [Extent4].[GradeId]
LEFT OUTER JOIN [dbo].[GradGroups] AS [Extent5] ON [Extent4].[GroupId] = [Extent5].[Id]
As you see we have two joins for Grade and GradeGroup tables.
I used this library:
https://github.com/StefH/System.Linq.Dynamic.Core

Related

How To Bind Linq Join With ViewModel In Asp.net MVC?

Here I am trying to get the list by joining two table using LINQ and I want ContestantId from ContestantRatings table. But it shows an error in line ContestantId = Convert.ToInt32(CR.ContestantId) which says: 'LINQ to Entities does not recognize the method Int32 ToInt32(System.Object) method, and this method cannot be translated into a store expression.'
.
Any help will be highly appreciated.
Below is my code
public List<ContestantRating.Core.ViewModel.ContestantRatingVM> GetContestantRatingList()
{
var contestantRatingList = (from C in db.Contestants where C.IsActive==true
join CR in db.ContestantRatings on C.Id equals CR.ContestantId
select new ContestantRatingVM
{
ContestantId = Convert.ToInt32(CR.ContestantId),
FirstName = C.FirstName,
LastName = C.LastName,
DateOfBirth = C.DateOfBirth,
District = C.District.DistrictName,
Rating = CR.Rating,
RatingId = CR.Id,
RatedDate=CR.RatedDate
}).ToList().OrderByDescending(x => x.RatingId);
return contestantRatingList.ToList();
}
Below is my viewmodel ContestantRatingVM which I used
public class ContestantRatingVM
{
public int ContestantId { get; set; }
public Nullable<int> HomeZoneId { get; set; }
public string District { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public Nullable<System.DateTime> DateOfBirth { get; set; }
public Nullable<bool> IsActive { get; set; }
public string Gender { get; set; }
public string PhotoUrl { get; set; }
public string Address { get; set; }
public int RatingId { get; set; }
public Nullable<decimal> Rating { get; set; }
public Nullable<System.DateTime> RatedDate { get; set; }
}
The problem is the call to Convert.ToInt32. Linq to entities converts the Linq query to a database query and thus has a limited set of operations it supports. In order to solve this, you have several options.
One option is to do the conversion after the database query:
public List<ContestantRating.Core.ViewModel.ContestantRatingVM> GetContestantRatingList()
{
var tempArray = (from c in db.Contestants where C.IsActive==true
join cr in db.ContestantRatings on C.Id equals CR.ContestantId
select new { C = c, CR = cr }).ToArray();
var contestantRatingList = from x in tempArray select new ContestantRatingVM
{
ContestantId = Convert.ToInt32(x.CR.ContestantId),
FirstName = x.C.FirstName,
// ...
}).ToList().OrderByDescending(x => x.RatingId);
return contestantRatingList.ToList();
}
By calling ToArray, you send the query to the database, so that Linq to Enities does not have to deal with Convert.ToInt32.

select specific field in each joined table in LINQ sql query entity framework

public class OnLoginData
{
public List<TableDetails> lstTableDetails { get; set; }
public List<CategoryDetails> lstCategoryDetails { get; set; }
}
public class TableDetails
{
public int TableId { get; set; }
public int TableNumber { get; set; }
public string TableName { get; set; }
}
public partial class CategoryDetails
{
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public string Description { get; set; }
public string Image { get; set; }
public long? SystemId { get; set; }
public int? SortId { get; set; }
}
var queryLoginAdimin = from admin in conDb.SystemAdminMasters
join system in conDb.SystemMasters on admin.SystemID equals system.SystemId into SM
join category in conDb.MenuCategoryMasters on admin.SystemID equals category.SystemId into CM
join menu in conDb.MenuMasters on admin.SystemID equals menu.SystemId into MM
join table in conDb.TableMasters on admin.SystemID equals table.SystemId into TM
select new OnLoginData
{
lstTableDetails = TM.Select(o => new
{
o.TableId,
o.TableName,
o.TableNumber
}).ToList()
};
Please check the above code, I'm trying to do joins using multiple tables and I don't require all fields from database tables. I only need those fields where I have take a separate class TableDetails and CategoryDetails. I want to select those fields from above linq query and create a whole List that is On LoginData.
How do I do that?
You Can Query in Below Way
var queryLoginAdimin =
from admin in conDb.SystemAdminMasters
join system in conDb.SystemMasters on admin.SystemID equals system.SystemId into SM
join category in conDb.MenuCategoryMasters on admin.SystemID equals category.SystemId into CM
join menu in conDb.MenuMasters on admin.SystemID equals menu.SystemId into MM
select new OnLoginData
{
lstTableDetails = conDb.TableMasters
.Where(table => table.SystemId == admin.SystemID)
.Select(o => new TableDetails
{
TableId = o.TableId,
TableName = o.TableName,
TableNumber = o.TableNumber
}),
//Same for lstCategoryDetails
};
You Can't use .ToList() inside select so you can change OnLoginData To IEnumerable
public class OnLoginData
{
public IEnumerable<TableDetails> lstTableDetails { get; set; }
public IEnumerable<CategoryDetails> lstCategoryDetails { get; set; }
}

Sequence contains no elements on query

Query:
var result = await this.Context.ShopProducts
.Include(prd => prd.Category)
.ThenInclude(cat => cat.Culture)
.Include(prd => prd.InfoItems)
.SingleOrDefaultAsync(prd => prd.Id.Equals(id) && prd.CategoryId.Equals(culture));
Edit: Updated the entities and query to reflect the new design and added a sql query
Entities:
Product:
[Table("ShopProduct")]
public class Product : ShopBase
{
public bool Active { get; set; } = true;
public int CategoryId { get; set; }
public virtual Category Category { get; set; }
public ICollection<ProductInfo> InfoItems { get; set; } = new HashSet<ProductInfo>();
}
ProductInfo:
[Table("ShopProductInfo")]
public class ProductInfo : ShopBase
{
public int ProductId { get; set; }
public int CultureId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public decimal Sum { get; set; }
public ICollection<GraphicItem> GraphicItems { get; set; }
}
What I want is to only select the ProductInfo objects with CultureId that equals the Category CultureId property. When selecting I provide the product Id and Category Id.
I want to replicate something like this sql query:
DECLARE #prdId INT,
#catId INT
SET #prdId = 1
SET #catId = 1
SELECT prd.*,
info.*,
cat.*
FROM ShopProduct prd,
ShopProductInfo info,
ShopCategory cat
WHERE prd.Id = #prdId
AND prd.CategoryId = cat.Id
AND cat.Id = #catId
AND cat.CultureId = info.CultureId
this error mean, linq query return some value otherwise give exception error

Joining two tables in one datagrid usig Linq C# WPF

I want to join two tables into one datagrid. table one: tblProjects - table two: tblEmployeeLoginDetails
In this method- private void FillProjectsDataGrid() I want to fill my datagrid using this coding:
dgViewProjects.ItemsSource = DC.tblProjects.Where<tblProject>(c => c.ProjectID != null)
.Select<tblProject, ProjectData>(m => new ProjectData()
{
ProjectID = m.ProjectID,
Name = m.ProjectName,
Status = m.ProjectStatus,
Employee = m.EmployeeName,
});
dgViewProjects.ItemsSource = DC.tblEmployeeLoginDetails.Where<tblEmployeeLoginDetail>(c => c.LoginID != null)
.Select<tblEmployeeLoginDetail, EIDData>(m => new EIDData()
{
UserID = m.LoginID,
Name = m.EmployeeName,
Surname = m.EmployeeSurname,
Email = m.EmployeeEmailAddress,
Password = m.EmployeePassword,
Role = m.RoleID.ToString(),
Department = m.EmployeeDepartment,
IDNumber = m.EmployeeIDNumber,
Gender = m.EmployeeGender,
Date = m.EmployeeDOB.Value,
HomeAddress = m.EmployeeHomeAddress,
Telephone = m.EmployeeTelephoneNumber,
City = m.EmployeeCity,
Province = m.EmployeeProvinceCode,
SetImage = m.EmployeeProfilePicture
});
For tblProjects I use this class:
public struct PDData
{
public string _project;
public int ProjectID { get; set; }
public string Name { get; set; }
public string Status { get; set; }
public string Employee { get; set; }
}
For tblEmployeeLoginDetails I use this class:
public struct EIDData
{
public string _sts;
public int UserID { get; set; }
public string Name { get; set; }
public string Surname { get; set; }
public string Email { get; set; }
public string Password { get; set; }
public string Role { get; set; }
public string Department { get; set; }
public string IDNumber { get; set; }
public string Gender { get; set; }
public DateTime Date { get; set; }
public string HomeAddress { get; set; }
public string Telephone { get; set; }
public string City { get; set; }
public string Province { get; set; }
public Binary SetImage { get; set; }
}
Now I want to join these two tables and display the information in one datagrid. I have tried a Linq join statement but I get this error:
An unhandled exception of type 'System.InvalidCastException' occurred
in Haze.exe
Additional information: Unable to cast object of type
'System.Data.Linq.DataQuery1[Haze.tblProject]' to type
'System.Collections.Generic.List1[Haze.tblProject]'.
And lastly here is my join coding:
List<tblProject> Join = (List<tblProject>)from u in DC.tblEmployeeLoginDetails
join b in DC.tblProjects
on u.LoginID equals b.ProjectID
where b.ProjectID != null
select b;
I have no idea how to join these two tables together using the classes that I have to display it in one datagrid. If anyone has any advice or help, please don't hesitate to leave a comment. Thank you!
This will give you a new anonymous class with both the employee and project details from your join. The join you have was only returning values for the projects
var Join = (from u in DC.tblEmployeeLoginDetails
join b in DC.tblProjects
on u.LoginID equals b.ProjectID
where b.ProjectID != null
select new {Project=a, Employee=b}).ToList();
Then you would have to bind this to your datatable, something like this:
dgViewProjects.ItemsSource = Join;
Your first block of code is going to overwrite the initial setting of the datasource with the second, not add to it.
Did you try
List<tblProject> Join = (from u in DC.tblEmployeeLoginDetails
join b in DC.tblProjects
on u.LoginID equals b.ProjectID
where b.ProjectID != null
select b).ToList();

How to implement Entity Framework Code First join

I'm starting to use Entity Framework Code First.
Suppose to have such POCO's (ultimately simplified):
public class BortStructure
{
public Guid Id { get; set; }
public String Name { get; set; }
}
public class Slot
{
public Guid Id { get; set; }
public String Name { get; set; }
public BortStructure { get; set; }
}
public class SystemType
{
public Guid Id { get; set; }
public String Name {get; set; }
}
public class SlotSystemType
{
public Guid Id { get; set; }
public Slot Slot { get; set; }
public SystemType SystemType {get; set; }
}
and a context
public MyContext : DbContext
{
public DbSet<BortStructure> BortStructures { get; set; }
public DbSet<Slot> Slots{ get; set; }
public DbSet<SystemType> SystemTypes { get; set; }
public DbSet<SlotSystemType> SlotSystemTypes { get; set; }
}
I have a task to get BortStructure by Id with list of attached Slots, each one with list of systemTypes attached.
Using SQL allowed me to do that with some JOIN's:
SELECT BortStructures.Id, BortStructures.Name, Slots.Id,
Slots.Name, SystemType.Id, SystemType.Name FROM
((BortStructures LEFT JOIN Slots ON BortStructures.Id = Slots.BortStructureId)
LEFT JOIN SlotSystemTypes ON SlotSystemTypes.SlotId = Slots.Id)
LEFT JOIN SystemTypes ON SystemTypes.Id = SlotSystemTypes.SystemTypeId
WHERE BortStructures.Id='XXXXXX' ORDER BY Slots.Id, SystemType.Id
But with Entity Framework Code First I don't have any idea howto do that.
If I use
var slotSystemTypes = from sl in MyContext.SlotSystemTypes
where sl.Slot.BortStructure.Id = XXXXXX
orderby sl.Slot.Id, sl.SystemType.Id
select sl;
i, of course, will receive nothing if BortStructure consists of no Slots/Slots without any SystemTypes attached.
Instead of getting BortStructure with empty list of Slots/with Slots, each one with empty list of SystemTypes attached as I expect to get.
Is there any way to archive that with single LINQ query for my database configuration?
You can use join operator example:
string[] categories = new string[]{
"Beverages",
"Condiments",
"Vegetables",
"Dairy Products",
"Seafood" };
List<Product> products = GetProductList();
var q =
from c in categories
join p in products on c equals p.Category
select new { Category = c, p.ProductName };
foreach (var v in q)
{
Console.WriteLine(v.ProductName + ": " + v.Category);
}
more samples in: http://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9

Resources