How to QueryMultiple using linq?

How to select like the code below using LINQ or can I used QueryMultiple on LINQ?. I know how to join two model list using LINQ but with this situation, I don’t know how to do this using LINQ.

var result = con.QueryMultiple(@"select gtp.status, gtp.p_id, gtp.p_name, gtp.p_price,
                      gca.c_type, gbr.bedtype, gtp.currency from t_package as gtp, category as gca,
                      bedrooms as gbr where gtp.c_id = gca.c_id and gtp.bedroom = gbr.bed_id
                      and gtp.p_id = @id;
                      select gtd.tdates_id, gtd.dfrom, gtd.dto from traveldates as gtd
                      where gtd.p_id = @id;
                      select ginc.in_id, ginc.name from inclusion as ginc 
                      where ginc.p_id = @id;
                      select con.con_id, con.name from tcondition as con
                      where con.p_id = @id;
                      select exc.ex_id, exc.name from exclusion as exc 
                      where exc.p_id = @id;
                      select fli.fl_id, fli.name from flightdetails as fli
                      where fli.p_id = @id;
                      select iti.it_id, iti.name, iti.description, iti.bmeal, iti.lmeal, iti.dmeal from itinerary as iti
                      where iti.p_id = @id;
                      select img.img_id, img.slides from imagetable as img
                      where img.p_id = @id;
                      select vi.visa_id, vi.name from visareq as vi
                      where vi.p_id = @id;
                      select ph.ph_id, ph.ph_loc, ph.ph_phtel from photel as ph 
                      where ph.p_id = @id;",
                       new { @id = id });
                var aTp = result.ReadSingle<AdminTP>();
                aTp.adminTDs = result.Read<AdminTD>().ToList();
                aTp.adminINCs = result.Read<AdminINC>().ToList();
                aTp.adminCONs = result.Read<AdminCON>().ToList();
                aTp.adminEXCs = result.Read<AdminEXC>().ToList();
                aTp.adminFLIs = result.Read<AdminFLI>().ToList();
                aTp.adminITIs = result.Read<AdminITI>().ToList();
                aTp.adminIMGs = result.Read<AdminIMG>().ToList();
                aTp.adminVIs = result.Read<AdminVI>().ToList();
                aTp.photels = result.Read<Photel>().ToList();
                return Json(aTp, JsonRequestBehavior.AllowGet);

Lets assume t_package, traveldates and other table is a model list not a DB table.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

So you have sequences of Packages, TravelDates, Gincs, Cons, Flis, etc, and there seems to be one-to-many relations between Packages and these other items.

Every Package has zero or more TDates, and every TDate belongs to exactly one Package, namely the Package that the foreign key PackageId refers to.

Similarly, every Ginc belongs to the Package that the foreign key refers to, every Con and Fli etc belongs to the one and only package that the foreign key PackageId refers to: all straightforward one-to-many relations

Requirement: given a packageId, give me the Package with this Id, with its travel data, its Gincs, its Flis, and all its other items with strange names.

To be able to do this using LINQ, you need to have access to the IQueryables. For instance, via DbContext.DbSet<...>

int packageId = ...
IQueryable<Package> packages = ...
IQueryable<TDate> tDates = ...
IQueryable<Fli> Flis = ...
...

var result = packages.Where(package => package.Id == packageId).Select(package => new
{
    // Select the package properties that you plan to use:
    Id = package.Id,
    Name = package.Name,
    Status = package.Status,
    ...

    TDates = tDates.Where(tDate => tDate.PackageId == package.Id).Select(tDate => new
    {
        Id = tDate.Id,
        DateFrom = tDate.DateFrom,
        DateTo = tDate.DateTo,
        ...
    })
    .ToList(),

    Flis = flis.Where(fli => fli.PackageId == package.Id).Select(fli => new
    {
         ...
    })
    .ToList(),

    ... etc: 
});

Method 2

I think you are looking for the Union command, you can use Linq’s query notation or dot notation. You will need to perform the select you mention in your comments before doing the union so that each object appended are of the same type. The code below is in dot notation as I find it easier to read:

string[] adminTDs = { "A", "B", "C" };
string[] adminINCs= { "D", "E", "F" };
string[] adminCONs = { "H", "I", "J" };
//Method Syntax
var MS = adminTDs
            .Union(adminINCs)
            .Union(adminCONs).ToList();

More on LINQ unions can be found at https://dotnettutorials.net/lesson/linq-union-method/.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x